Forum Discussion
TOPN Return One Value Only
Tries Highest Team =
TOPN(
1,
ALL( 'Team Stats'[Team] ), [Total Tries] )
Hi All
I am using the above in the new Reference Label feature in the New Card Visual
When this returns more than one value, ie. there is a tie then the whole visual breaks, is there a way to amend the DAX above to just show one value?
Well, you will need a "Tie breaker"; so if you have multiple teams you could for instance take the first team by name:
Tries Highest Team = TOPN( 1, ALL( 'Team Stats'[Team] ) , [Total Tries],0 ,'Team Stats'[team], 1 )Alternatively, you could also show all teams tied for first place, comma sepperated;
Tries Highest Team = VAR tms = TOPN( 1, ALL( 'Team Stats'[Team] ) , [Total Tries] ) RETURN CONCATENATEX(tms, [Team],",")Hi,
I am not sure how you want to show only one when there are two result that show top number, but please try something like below if it suits your requirement.
The second criteria to decide the rank in the below is the team-name ascending order.
INDEX function (DAX) - DAX | Microsoft Learn
Tries Highest Team = INDEX( 1, ALL('Team Stats'[Team]), ORDERBY( [Total Tries], DESC, 'Team Stats'[Team], ASC ) )
3 Replies
Hi,
I am not sure how you want to show only one when there are two result that show top number, but please try something like below if it suits your requirement.
The second criteria to decide the rank in the below is the team-name ascending order.
INDEX function (DAX) - DAX | Microsoft Learn
Tries Highest Team = INDEX( 1, ALL('Team Stats'[Team]), ORDERBY( [Total Tries], DESC, 'Team Stats'[Team], ASC ) )Well, you will need a "Tie breaker"; so if you have multiple teams you could for instance take the first team by name:
Tries Highest Team = TOPN( 1, ALL( 'Team Stats'[Team] ) , [Total Tries],0 ,'Team Stats'[team], 1 )Alternatively, you could also show all teams tied for first place, comma sepperated;
Tries Highest Team = VAR tms = TOPN( 1, ALL( 'Team Stats'[Team] ) , [Total Tries] ) RETURN CONCATENATEX(tms, [Team],",")Jihwan_Kim , Thanks for sharing this is really helpful
