Count combinations

[Anonymous]

Hi everybody,

I hope someone can help me to calculate the number of combinations with Power BI.

I have a data set with a personal number in column A. This number is therefore more common in the table. Column B shows the type of facility this person has. However, one person can have several (different) facilities. The personal number can therefore appear several times in column A. Now I would like to create a table / graph showing the combinations of facilities. So for example: there are 10 people who have both provision x and y. There are 3 persons who have provision x, as y and z. Etc. etc.

I hope I have expressed my question so clearly. If not, let me know.

Thank you in advance.

Greet Joost

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario.

Table:

You may create a calculated column and a measure as below.

calculated column:
Combinations = 
CONCATENATEX(
    FILTER(
        'Table',
        'Table'[ColumnA] = EARLIER('Table'[ColumnA])
    ),
    'Table'[ColumnB]
)

Measure:
Count Combinations = 
var x = SELECTEDVALUE('Table'[Combinations])
return
CALCULATE(
    DISTINCTCOUNT('Table'[ColumnA]),
    FILTER(
        ALLSELECTED('Table'),
        CONTAINSSTRINGEXACT('Table'[Combinations],x)
    )
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Anonymous]

Thxs so much! This is where i was looking for!

[Greg_Deckler]

I have an example of combinations and permutations in my new DAX Cookbook. If you can post some sample data I can see if it can be applied in your situation. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Anonymous]

Thxs for you answer! But the sollution of v-alq-msft already did the trick.