Unique Combinations

[bbwong]

Trying to find best way to identify combinations and to count distinct IDs.

For example:

Would like output as this:

With thanks,

BB

[selimovd]

Hey @bbwong ,

you can do that with DAX measures. An approach like this should work:

Red & Blue = 
CALCULATE(
    COUNTROWS( myTable ),
    myTable[Category] = "RED" || myTable[Category] = "BLUE"
)

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[bbwong]

Hi @selimovd ,

Thank you for your prompt reply.

For the Red and Blue combination, your DAX measure is returning a value of 8 (counting each row containing red or green in the Category column, rather than giving me distinct count of ID = 2.

For example:

ID123 effectively has a combination of RED, RED, BLUE and ID 1011 has a combination of RED, BLUE, BLUE. I would like to ignore duplicate categories and define this as a RED & BLUE combination.

Hope I'm making sense.

Cheers,

Bee Bee

[selimovd · Accepted solution]

Hey @bbwong ,

you're absolutely right.

In this case change the COUNTROWS to a DISTINCTCOUNT of the ID:

Red & Blue = 
CALCULATE(
    DISTINCTCOUNT( myTable[ID] ),
    myTable[Category] = "RED" || myTable[Category] = "BLUE"
)

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic