Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello everyone,
I have the following table that lists all the shifts published this year. Each shift is a separate row.
I'm trying to show the Top 5 staff with the most shifts by using a Top N filter on Hours however since I have staff with the same hours, I'm getting more than 5 results. I understnad that this is where the RANKX fucniton can be useful but I was not able to apply it to my case.
Any hints or ideas?
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
Hi,
Create calculated column as-
You will get Dense Rank you can use it according to requirement.
Please mark it as as solution it soles your query.
Thanks
Naveen Mechu
I added the calculated coloumn but my graph is still showing more than 5 entries. My calculated coloumn is 'Top 5 by Hours'
Hi ,
Just put this Calculated Column as a simple filter and select Rank less than or equal to 5.
Thanks
Naveen Mechu
Just tried that too but no changes happen to the visual at all.
Hi, @SusuYes
Try something like:
Top5 =
VAR TopN = 5
VAR ranks =
RANKX(
‘YourTable’,
‘YourTable'[Hours],
,
,
Dense
)
RETURN
IF ( ranks <= TopN, ranks, BLANK () )
Proud to be a Super User!
I tried adding the first measure but I got this error.
I tried adding SUM(TABLE NAME[Hours]) but now it's just showing the value 1 for all entries
any idea what went wrong?
Hi,
Share the link from where i can download your PBI file. Via text boxes, show the problem clearly and also the expected result.
Hi,
You may download my PBI file from here.
Hope this helps.
Here is a link to a sample PBI file: https://bit.ly/3Fi1QEc
I want my bar graph to only show the top 5 staff who have the most hours. The bar graph should only show 5 enteries only.
Hi,
That link has expired.
I cant share the document as it has sensitive data but I will create a sample and share soon.
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |