Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Count combinations

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

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    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

4 Replies

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

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