FACT

How to Use the Power BI DAX function FACT

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:

FACT(number)

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(

FILTER(

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(

Department,

FACT([Department Combination])

)

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)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)