topic How to count by two categories with DAX ? in DAX Commands and Tips

How to count by two categories with DAX ?

Anonymous — Fri, 19 Apr 2024 12:43:15 GMT

Hey everyone, I have my table one, who look like this :

Location	X	Y
Location_1	A	B
Location_1	B	C
Location_2	A	C
Location_4	C	A
...	...	...

I've created another table manually to which I'd like to add the count of each note by Location in another column, but I can't manage to code it in DAX to do this, can someone help me?

Expected output :

Location	Rank	Count_X	Count_Y
Location_1	A	56	10
Location_2	A	38	16
Location_3	A	23	63
Location_4	A	12	65
Location_1	B	75	26
Location_2	B	83	37
Location_3	B	94	74
Location_4	B	35	73
Location_1	C	64	15
Location_2	C	194	64
Location_3	C	285	25
Location_4	C	186	241

(All numbers are here for example)

Thanks all for any help !

Re: How to count by two categories with DAX ?

lbendlin — Sat, 20 Apr 2024 19:17:55 GMT

Unpivot your "Table one" to bring it into usable format.

Then you can use implicit measures to achieve your result, no need for DAX.

Re: How to count by two categories with DAX ?

Anonymous — Tue, 23 Apr 2024 07:18:11 GMT

Hi Anonymous

lbendlin Thank you very much for your prompt reply, and allow me to add something here.

As lbendlin mentioned, you need to do unpivot columns for column "X" and column "Y" and then do the calculations.

Here's some dummy data

“Table”

Select two columns in the power query and click unpivot columns.

Create measures.

Count_Y = CALCULATE( COUNTROWS('Table'), FILTER( ALL('Table'), 'Table'[Location] = MAX('Table'[Location]) && 'Table'[Attribute] = "Y" && 'Table'[Rank] = MAX('Table'[Rank]) ) )

Count_X = CALCULATE( COUNTROWS('Table'), FILTER( ALL('Table'), 'Table'[Location] = MAX('Table'[Location]) && 'Table'[Attribute] = "X" && 'Table'[Rank] = MAX('Table'[Rank]) ) )

Here is the result.

Regards,

Nono Chen

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