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 1 Test1 Okay 74 2 Test2 Poor 24 3 Test3 Okay 15 4 Test4 Ok...
Need Help
amitchandak's avatar
amitchandak
Super User
4 years ago

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