Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Trying to find best way to identify combinations and to count distinct IDs.
For example:
Would like output as this:
With thanks,
BB
Solved! Go to 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"
)
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"
)
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
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"
)
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
90 | |
82 | |
62 | |
61 | |
58 |
User | Count |
---|---|
160 | |
114 | |
100 | |
74 | |
65 |