What is the FACT Function?
The FACT function in Power BI is used to calculate the factorial of a number. A factorial is the product of an integer and all the integers below it. For example, the factorial of 5 (written as 5!) is 5 x 4 x 3 x 2 x 1 = 120. The FACT function is especially useful when dealing with calculations involving permutations and combinations.
Syntax of the FACT Function
The syntax of the FACT function is straightforward. It takes only one argument – the number for which the factorial is to be calculated. The syntax is as follows:
Here, the number argument can be any valid expression that evaluates to a positive integer. The FACT function returns a decimal value that represents the factorial of the specified number.
How to Use the FACT Function
Using the FACT function in Power BI is relatively simple. Here’s an example to illustrate how it works. Suppose you have a dataset that contains the number of employees in each department of a company. You want to calculate the total number of unique combinations of departments that can be formed. To do this, you will use the FACT function along with other DAX functions.
First, create a new calculated column in your dataset by going to the Modeling tab and selecting New Column. Name the new column “Department Combination” and enter the following DAX formula:
Department Combination = COMBIN(N, k)
Here, N represents the total number of departments, and k represents the number of departments in each combination. To calculate N, you can use the COUNTROWS function:
N = COUNTROWS(Department)
Next, you need to calculate k. To do this, you can use the RANKX function to rank the departments by the number of employees, and then use the MINX function to select the top k departments:
k = MINX(
RANKX(Department, [Number of Employees], , ASC),
RANKX(Department, [Number of Employees], , ASC) ❰= k
[Number of Employees]
Finally, to calculate the total number of unique combinations, you can use the FACT function:
Total Combinations = SUMX(
This formula calculates the factorial of the Department Combination column for each row in the dataset and then sums up the results.
In this article, we have explained what the FACT function is and how to use it in Power BI. The FACT function is a powerful tool for calculating factorials, which can be especially useful in calculations involving permutations and combinations. By combining the FACT function with other DAX functions, you can perform more advanced data modeling and analysis in Power BI.
Power BI DAX Training Courses by G Com Solutions (0800 998 9248)
Power BI DAX Intensive Training Course£1,050.00 – £26,550.00
Power BI DAX Introduction£395.00 – £9,750.00
Power BI DAX Intermediate£395.00 – £9,750.00
Power BI DAX Advanced£395.00 – £9,750.00