Forum Discussion
DAX expression to count value and return the label
Hi all - I'm trying to create a DAX forumla that will return a data label (or value or category, not sure what to call it) based on the raw data below:
| Winner |
| Andy |
| Brenda |
| Craig |
| Denise |
| Andy |
| Brenda |
| Craig |
| Craig |
| Denise |
| Denise |
| Denise |
| Andy |
| Andy |
| Andy |
| Andy |
| Andy |
The summary data is:
| Winner | Won |
| Andy | 7 |
| Brenda | 2 |
| Craig | 3 |
| Denise | 4 |
| TOTAL | 16 |
We can see from the above table, that the "category" that won the most is Andy. I'm trying to create two DAX expressions that will count the total won by each category, and then return the name of that category, and the value of the category. So the results of each DAX expression would be: Andy and 7.
Is this possible? I hope I've explained this well enough. Thank you!
1. DAX Expression to Return the Name of the Winner with the Most Wins
MostWinsWinner =
CALCULATE(
VALUES('Table'[Winner]),
TOPN(
1,
SUMMARIZE(
'Table',
'Table'[Winner],
"Wins", COUNT('Table'[Winner])
),
[Wins],
DESC
)
)2. DAX Expression to Return the Count of the Most Wins
MostWinsCount =
CALCULATE(
MAXX(
SUMMARIZE(
'Table',
'Table'[Winner],
"Wins", COUNT('Table'[Winner])
),
[Wins]
)
)
5 Replies
1. DAX Expression to Return the Name of the Winner with the Most Wins
MostWinsWinner =
CALCULATE(
VALUES('Table'[Winner]),
TOPN(
1,
SUMMARIZE(
'Table',
'Table'[Winner],
"Wins", COUNT('Table'[Winner])
),
[Wins],
DESC
)
)2. DAX Expression to Return the Count of the Most Wins
MostWinsCount =
CALCULATE(
MAXX(
SUMMARIZE(
'Table',
'Table'[Winner],
"Wins", COUNT('Table'[Winner])
),
[Wins]
)
)Thank you so much - you've saved me so much time 😁
OH wow, thank you so much! It was hard to look up the response myself because I didn't even know what search terms to use! But this is perfect, thank you so much!
1. DAX Expression to Return the Name of the Winner with the Most WinsMostWinsWinner = CALCULATE( VALUES('Table'[Winner]), TOPN( 1, SUMMARIZE( 'Table', 'Table'[Winner], "Wins", COUNT('Table'[Winner]) ), [Wins], DESC ) )
2. DAX Expression to Return the Count of the Most Wins
MostWinsCount =
CALCULATE(
MAXX(
SUMMARIZE(
'Table',
'Table'[Winner],
"Wins", COUNT('Table'[Winner])
),
[Wins]
)
)
