topic Re: Count distinct rows based on multiple columns in DAX Commands and Tips

Count distinct rows based on multiple columns

Anonymous — Thu, 30 Sep 2021 01:20:15 GMT

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

Re: Count distinct rows based on multiple columns

VahidDM — Thu, 30 Sep 2021 02:30:12 GMT

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

Re: Count distinct rows based on multiple columns

Jihwan_Kim — Thu, 30 Sep 2021 03:03:10 GMT

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"

)