cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

How to count by two categories with DAX ?

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 !

2 ACCEPTED SOLUTIONS
Super User

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

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

Community Support

Hi @Ouhla

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.

2 REPLIES 2
Community Support

Hi @Ouhla

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.

Super User

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

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