cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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.

Announcements

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors