topic Re: Top 5 with tiebreaker inconsistencies in DAX in DAX Commands and Tips

Top 5 with tiebreaker inconsistencies in DAX

AnnieV — Tue, 29 Jul 2025 21:26:18 GMT

Goal: Create a table visual that contains 5 survey questions, ranked by a score called the PriorityIndex, with ties broken by a GapToGoal value.

Sort is on PriorityIndex value DESC, so highest number is rank 1.

Tiebreaker is on GapToGoal value, which can be a negative number if the question is performing under goal, or positive if it is performing over goal. While not impossible, a question is highly unlikely to have exactly the same PriorityIndex and GapToGoal.
A lower number would be the "winner" in the tie break, so two questions with PriorityIndex of 35, and one has a GapToGoal of -4.2 and the other -3.8, the question with the -4.2 gap is the "winner" and would rank 1st before the other.

My current measure for Top5Rank is:

Top5Rank =

RANKX(

ALLSELECTED('NewPriorityIndex'),

CALCULATE(

MAX('NewPriorityIndex'[PriorityIndex]) * 1000000

- [Gap to Goal] -- Gap to Goal is a measure, smaller is better

DESC,

DENSE

)

Then I have a filter visual as:

Top5Filter =

IF([Top5Rank] <= 5, 1, 0)

That is set to "is 1"

The current sample I'm reviewing does display only items with rank 1 to 5, but there's multiples, the Top5Rank values are 1,2,3,3,4,4,5,5,5,5,5, so I get far more than 5 items.

If I change DENSE to SKIP in the Top5Rank measure, I end up with 4 items, rank 1, 4, 5, 5 on the same list, given it will only display those equal to or less than 5, the rank list looks like: 1,4,5,5,10,10,24...

I have done troubleshooting by creating a RankScore measure showing the value calculated in the Top5Rank measure, which shows where a problem in value sorting, but not sure how to correct for it (values with the SKIP version of the Top5Rank measure):

Rank 1 : 42,000,001.37
Rank 4 : 41,999,997.23
Rank 5 : 40,000,000.39
Rank 5: 40,999,999.60 << Should be third in this list
Rank 10: 39,999,996.23 << Should be sixth in this list
Rank 10: 39,999,998.45 << Should be fifth in this list

Working with AI there's been various suggestions about context, but nothing has made a correct change.

Thoughts?

Re: Top 5 with tiebreaker inconsistencies in DAX

Irwan — Tue, 29 Jul 2025 22:26:32 GMT

hello AnnieV

the same rank means you have exact same value in your expression.

you need another conditional to define which is higher when there are same values, then using if statement when same values occur with that pre-defined condition.

otherwise please share your sample data that represent your original data including your expected/desired outcome (remove any confidential information).

Thank you.

Re: Top 5 with tiebreaker inconsistencies in DAX

AnnieV — Wed, 30 Jul 2025 13:15:19 GMT

Hi Irwan ,

So after writing up a very detailed explanation, and putting together a file with just some sample data, it's performing as expected, which tells me there has to be some other filter context or condition going on where I'm using this that's impacting the results.

It's a good reminder to isolate things that are going wrong, to check if it's working as expected without any other interference.

Re: Top 5 with tiebreaker inconsistencies in DAX

Irwan — Wed, 30 Jul 2025 22:24:51 GMT

hello AnnieV

glad you solved the problem.

Thank you.