Forum Discussion
How TOPN handles Ties
Hi, I thought that TOPN , returned all values when I tie occors ; so top 3 ; 1, 2,, 2, 3,3, 4,5 = 1,2,2,3,3,
But not sure I have this to scenarios;
the Same TOPN( 3, measure for each moth ; this return 4 dates for 25, 17, 12, , 12
| 03/09/2020 | A | 17 |
| 09/09/2020 | D | 12 |
| 17/09/2020 | D | 25 |
| 23/09/2020 | E | 12 |
| 28/09/2020 | C | 5 |
but this only return 3 dates for 24, 18 , 18 , so it that all values are returned with the tie is for the Nth postion, and other
ties are 'pushed down' the ranking ?
| 01/02/2020 | A | 18 |
| 07/02/2020 | E | 24 |
| 15/02/2020 | C | 18 |
| 25/02/2020 | A | 11 |
Hi Dicken - You're correct in observing that TOPN in DAX can sometimes yield different results based on how ties are handled at the Nth position.
TOPN returns all the rows that tie for the Nth position. For example, if you're looking for the top 3 values, and the values at positions 3 and 4 are tied, TOPN will return both values. This is why you get four rows in your first example (since both 12s tie for the third position).
When Ties Don’t Reach the Nth Position: In contrast, if the tied values appear before or after the Nth position without affecting it directly, TOPN will only return up to the specified N rows. In your second example, since there is no additional tie at the exact 3rd position, TOPN(3) just returns three rows (24, 18, and 18).
If you need more precise control over the number of rows (e.g., always limiting to exactly N rows), you can adjust the ranking or filter criteria to prevent ties at the cutoff point. Alternatively, you might consider using RANKX to assign a unique rank to each row based on a secondary criterion, like Date, to avoid ties.
Hope the above details helps you.
Hi Dicken -You're exactly right! The behavior you’re observing in TOPN with ties does indeed resemble "tie-breaking" logic often seen in rankings, like in golf, where ranks can be "skipped" based on the number of ties at each position.
This is normal behavior and happens because TOPN essentially considers ranks based on values rather than limiting the result to a strict count when ties are present.
If you'd like help with a specific measure or formula to handle a custom tie-breaking logic, feel free to share more details.
Hope the above information helps.
3 Replies
Hi Dicken - You're correct in observing that TOPN in DAX can sometimes yield different results based on how ties are handled at the Nth position.
TOPN returns all the rows that tie for the Nth position. For example, if you're looking for the top 3 values, and the values at positions 3 and 4 are tied, TOPN will return both values. This is why you get four rows in your first example (since both 12s tie for the third position).
When Ties Don’t Reach the Nth Position: In contrast, if the tied values appear before or after the Nth position without affecting it directly, TOPN will only return up to the specified N rows. In your second example, since there is no additional tie at the exact 3rd position, TOPN(3) just returns three rows (24, 18, and 18).
If you need more precise control over the number of rows (e.g., always limiting to exactly N rows), you can adjust the ranking or filter criteria to prevent ties at the cutoff point. Alternatively, you might consider using RANKX to assign a unique rank to each row based on a secondary criterion, like Date, to avoid ties.
Hope the above details helps you.
Thanks, yes, I'd sort of got figured just putting differenct scenario's in and see what I got,
it seems ( and this may not help) like ties in golf, where you can have 5 places but if the first
is a two way tie and the second a 3 way then the all 5 places filled,Hi Dicken -You're exactly right! The behavior you’re observing in TOPN with ties does indeed resemble "tie-breaking" logic often seen in rankings, like in golf, where ranks can be "skipped" based on the number of ties at each position.
This is normal behavior and happens because TOPN essentially considers ranks based on values rather than limiting the result to a strict count when ties are present.
If you'd like help with a specific measure or formula to handle a custom tie-breaking logic, feel free to share more details.
Hope the above information helps.
