Count distinct rows based on multiple columns

[Anonymous]

Hi 

Can someone please help me with the Dax?
Need to count distinct rows based of three different columns - Category A, Catgeory B and Color = Red
If the row is duplicate - count only once. 

Sample Table -

Expected Output - 

Category A	Count
A	3
B	1

Thanks in advance

[VahidDM · Accepted solution]

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🙏!!

[Jihwan_Kim · Accepted solution]

Hi,

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

Expected result measure : =

COUNTROWS (

FILTER (

SUMMARIZE ( Data, Data[Category B], Data[Category C], Data[Color] ),

Data[Color] = "red"

)

)

[VahidDM · Accepted solution]

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🙏!!