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 tab...
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

v-alq-msft's avatar
v-alq-msft
Icon for Community Support rankCommunity 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!