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