How to select Top/Bottom N results dynamically

[ebecerra]

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

Id      TestName Grade RunTime

1	Test1	Okay	74
2	Test2	Poor	24
3	Test3	Okay	15
4	Test4	Okay	33
5	Test5	Poor	93
6	Test6	Good	61
7	Test7	Great	50
8	Test8	Okay	200
9	Test9	Great	10
222	Test222	Poor	184
223	Test223	Poor	101
224	Test224	Poor	101
225	Test225	Poor	100
226	Test226	Poor	99
227	Test227	Poor	101

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!

[Ashish_Mathur]

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.

[amitchandak]

@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