Power Pivot Principles: The A to Z of DAX Functions – COMBIN

In our long-established Power Pivot Principles articles, we continue our series on the A to Z of Data Analysis eXpression (DAX) functions. This week, we look at COMBIN.

The COMBIN function

Are you often COMBIN the DAX functions to see how many subsets you can make?  This function returns the number of combinations for a given number of items (i.e. the number of distinct subsets of items where order is unimportant).  You should use COMBIN to determine the total possible number of groups for a given number of items.

The COMBIN function employs the following syntax to operate:

The COMBIN function has the following arguments:

• number: this is required and represents the number of items
• number_chosen: this is also required.  This denotes the number of items in each combination.

It should be further noted that:

• numeric arguments are truncated to integers
• if either argument is nonnumeric, COMBIN returns the #VALUE! error value
• if number < 0, number_chosen < 0, or number < number_chosen, COMBIN returns the #NUM! error value
• a combination is any set or subset of items, regardless of their internal order.  Combinations are distinct from permutations, for which the internal order is significant
• the number of combinations is as follows, where number = n and number_chosen = k:

where:

• and n! denotes n x (n – 1) x … x 2 x 1
• this function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

Please see my example below:

Sorry to ruin the ending of Series 927 for you…

Come back next week for our next post on Power Pivot in the Blog section. In the meantime, please remember we have training in Power Pivot which you can find out more about here. If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.