Number of unique rows – by multiple columns
In DAX is quite simple to get the number o unique values – just use the DISTINCTCOUNT function. But how to do it, if you need multiple columns to recognize the duplicate?
For example here I´d like to know, how many animals and colors are there. The result should be 4 – there are 5 rows, but the grey pig appears two times.
Measure, calculating unique values based on two columns, looks like this:
- Number of unique values = COUNTROWS(
SUMMARIZE(
‘Table’,
‘Table'[Animal],
‘Table'[Color]))
Why?
The logic is, that the COUNTROWS functions calculates rows from table, that was previously created by SUMMARIZE, which removes duplicates now in this syntax.
Notes:
- This measure can be simply used in any visual in any filter context
- Instead SUMMARIZE you can similarly use GROUPBY