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(
The logic is, that the COUNTROWS functions calculates rows from table, that was previously created by SUMMARIZE, which removes duplicates now in this syntax.
- This measure can be simply used in any visual in any filter context
- Instead SUMMARIZE you can similarly use GROUPBY