Count distinct rows based on multiple columns

Hi Anonymous 

It seems your calculation was based on 4 columns because row 5 in your data is the same as row 1 based on Category A and Category B, and Color. (so, in the result for A, it should be 2, not 3).

But if you want the result based on the three categories and color=Red,

1- Add a new colum with this code :

New Column = 
'Table'[Category A]&'Table'[Category B]&'Table'[Category C]
//If you want the distinc count based on Cat A and B, remove "&'Table'[Category C]" from end of the code

2- try this measure to find the count distinct rows:

Measure = 
CALCULATE (
    DISTINCTCOUNT ( 'Table'[New Column] ),
    FILTER ( 'Table', 'Table'[Color] = "Red" )
)

Output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos🙏!!

Hi,

In my opinion, if you are "counting rows - distinct" based on more than two columns, the DAX function - SUMMARIZE helps.