Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
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
User | Count |
---|---|
102 | |
92 | |
85 | |
78 | |
71 |
User | Count |
---|---|
113 | |
104 | |
101 | |
73 | |
65 |