Forum Discussion
Unique Combinations
Trying to find best way to identify combinations and to count distinct IDs.
For example:
Would like output as this:
With thanks,
BB
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 regardsDenisBlog: WhatTheFact.biFollow me: twitter.com/DenSelimovic
3 Replies
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 regardsDenisBlog: WhatTheFact.biFollow me: twitter.com/DenSelimovicHi 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
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 regardsDenisBlog: WhatTheFact.biFollow me: twitter.com/DenSelimovic
