COMBIN

How to Use the Power BI DAX function COMBIN

What is the COMBIN function?

The COMBIN function in Power BI is a DAX function that returns the number of combinations of a given number of objects. In simpler terms, it calculates the number of ways you can choose a number of elements from a larger set, without regard to order.

The syntax for the COMBIN function is as follows:

COMBIN(n, k)

Here, `n` represents the total number of objects, and `k` represents the number of objects to choose.

Using the COMBIN function in Power BI

Now that we understand what the COMBIN function does, let’s take a look at how to use it in Power BI.

First, let’s consider a simple example. Suppose we have a list of five items, and we want to know how many ways we can choose two items from the list. In this case, we would use the COMBIN function as follows:

COMBIN(5,2)

The result of this calculation would be 10, which tells us that there are ten possible combinations of two items from the list of five.

Of course, in practice, our data is likely to be more complex than this. In Power BI, we can use the COMBIN function in a variety of ways to handle more complex scenarios. Here are a few examples:

Example 1: Using variables

Suppose we have a table with two columns: `Category` and `Sales`. We want to know how many ways we can choose two categories from the table. Here’s how we can use the COMBIN function to do this:

Sales Combinations =

VAR numCategories = COUNTROWS(VALUES(‘Table'[Category]))

RETURN

COMBIN(numCategories,2)

Here, we use a variable to count the number of unique categories in the table. We then pass this number to the COMBIN function to calculate the number of possible combinations.

Example 2: Using filters

Suppose we have a table with two columns: `Product` and `Sales`. We want to know how many ways we can choose two products with sales greater than $100. Here’s how we can use the COMBIN function with filters to do this:

Sales Combinations =

VAR products = FILTER(‘Table’, ‘Table'[Sales] ❱ 100)

VAR numProducts = COUNTROWS(products)

RETURN

COMBIN(numProducts,2)

Here, we use the FILTER function to select only the products with sales greater than $100. We then count the number of products that meet this criteria, and pass this number to the COMBIN function to calculate the number of possible combinations.

Example 3: Using the COMBINEVALUES function

Suppose we have a table with three columns: `Product`, `Color`, and `Sales`. We want to know how many ways we can choose two products with the same color. Here’s how we can use the COMBIN function with the COMBINEVALUES function to do this:

Sales Combinations =

VAR products = SUMMARIZE(‘Table’, ‘Table'[Color], “Products”, COMBINEVALUES(‘Table'[Product]))

VAR numColors = COUNTROWS(products)

VAR numCombinations = SUMX(products, COMBIN(COUNTROWS([Products]),2))

RETURN

numCombinations

Here, we use the SUMMARIZE function to group the products by color. We then use the COMBINEVALUES function to concatenate the product names into a single string for each color. We count the number of unique colors, and pass this number to the COMBIN function to calculate the number of possible combinations. Finally, we use the SUMX function to sum up the results for each color and get the total number of combinations.

In this article, we’ve explored how to use the COMBIN function in Power BI to calculate the number of combinations of a given number of objects. We’ve seen how to use variables, filters, and the COMBINEVALUES function to handle more complex scenarios. With this knowledge, you’ll be able to handle combinations efficiently in Power BI and take your data analysis to the next level.

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)