Forum Discussion
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 !
Unpivot your "Table one" to bring it into usable format.
Then you can use implicit measures to achieve your result, no need for DAX.
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.
2 Replies
- lbendlin
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.
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.