Forum Discussion

ebecerra's avatar
ebecerra
Microsoft Employee
4 years ago

How to select Top/Bottom N results dynamically

I have a relatively simple data set that looks something along the lines of this:

 

Id      TestName Grade RunTime

1Test1Okay74
2Test2Poor24
3Test3Okay15
4Test4Okay33
5Test5Poor93
6Test6Good61
7Test7Great50
8Test8Okay200
9Test9Great10
222Test222Poor184
223Test223Poor101
224Test224Poor101
225Test225Poor100
226Test226Poor99
227Test227Poor101

 

Additionally I have two helper tables to use as slicer values for the top N options and to select Top/Bottom.

 

I have a chart to show the Top/Bottom N tests using their RunTime as value to determine that logic. Additionally I have a dounut chart to show the percentage of grades in all my table. I'm using a way to use measures to be able to dynamically change the TOP/BOTTOM options to see.

 

 

 

TopN Grades by TestName = 
// Var to hold the value of the slicer
VAR SelectedTop = SELECTEDVALUE('Selected Top N'[Top N])

// Assign a rank to all controls based upon total visit count.
VAR RankValue = if(SELECTEDVALUE('Top or Bottom'[Option])=="Top",
RANKX(ALLSELECTED(TestResults[TestName]),[Total Grades Count],,DESC, Dense)<= SelectedTop, // All ranks that are less than or equal to my slicer value are returned.
RANKX(ALLSELECTED(TestResults[TestName]),[Total Grades Count],,ASC, Dense)<= SelectedTop) // All ranks that are less than or equal to my slicer value are returned.

RETURN
SWITCH(TRUE(),
    SelectedTop = 0,[Total Grades Count],
    RankValue,
    // Return all values if Input value is unknown
    [Total Grades Count]
)

 

 

 

When I select the Top option it all sort of works ( I added an extra table to try to debug what is going on):

 

And when I select the Bottom option my donut chart dissapears:

You can also see that the table I added does not repsect the Top N values selected, it always seems to show more than the selected value of 5 in this example. I can't figure out what I'm doing wrong and I hope someone can guide me in the right direction.  Here is the link to the sample pbix file for this dataset: Sample PBI 

 

Thanks!

2 Replies

  • Hi,

    See if this gets you started.  The Table and the bar chart works fine - not sure of the pie.  My solution works for Top only (not for bottom).  Download PBI file from here.

    Hope this helps.

  • ebecerra , Try TOPN for that

     

    TopN Grades by TestName =
    // Var to hold the value of the slicer
    VAR SelectedTop = SELECTEDVALUE('Selected Top N'[Top N])

    // Assign a rank to all controls based upon total visit count.
    VAR RankValue = if(SELECTEDVALUE('Top or Bottom'[Option])=="Top",
    CALCULATE([Total Grades Count], TOPN(SelectedTop,ALL(TestResults [TestName]),[Total Grades Count],dense), VALUES(TestResults [TestName])),
    CALCULATE([Total Grades Count], TOPN(SelectedTop,ALL(TestResults [TestName]),[Total Grades Count],asc), VALUES(TestResults [TestName]))
    )
    RETURN
    RankValue

     

    TOPN: https://www.youtube.com/watch?v=QIVEFp-QiOk&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=42