top5
1 Topic
Top 5 with tiebreaker inconsistencies in DAX
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?Solved601Views0likes3Comments