The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I have following data table and I want to write a dax for a measure such that I want to show the top 5 empid based on the average of marks, there are multiple entries for the same emp id as you can see in the data table, and I have multiple slicers and if any selection is done in any of the slicer then the table should show ranking as per the slicer selection and if nothing is selected in the slicer then the table should show overall ranking based on average of mapped block score and ranking should not consider "unavailable" emp id.
For Ex.
1)
Now in above scenario nothing is selected in any of the slicer, now the ranking should be based on overall average of score.
2)
Now in this condition, “DP2” is selected in a slicer X1_name, now the average of marks should get calculated for DP2 for that emp id and then the ranking should show based on that calculation.
Similar kind of ranking should appear as per respective selection in any of the slicer.
Below is the data table structure.
emp ID | name | Marks | x1_name | x2_name | x3_name | x4_name | Available/Unavailable | Rank (o/p - column) |
1 | A | DP1 | KJ1 | IC4 | YT4 | Available | ||
1 | A | 47 | DP2 | KJ5 | IC4 | YT5 | Available | |
1 | A | 12 | DP3 | KJ3 | IC1 | YT1 | Available | |
1 | A | DP4 | KJ5 | IC2 | YT2 | Available | ||
2 | B | DP1 | KJ3 | IC3 | YT3 | Available | ||
2 | B | DP2 | KJ4 | IC4 | YT4 | Available | ||
2 | B | DP3 | KJ4 | IC5 | YT1 | Available | ||
2 | B | 19 | DP4 | KJ2 | IC4 | YT2 | Available | |
2 | B | 45 | DP5 | KJ3 | IC2 | YT3 | Available | |
2 | B | 31 | DP4 | KJ4 | IC5 | YT4 | Available | |
3 | C | 42 | DP2 | KJ5 | IC3 | YT5 | Unavailable | |
3 | C | DP3 | KJ1 | IC4 | YT4 | Unavailable | ||
3 | C | DP4 | KJ2 | IC4 | YT3 | Unavailable | ||
4 | D | 28 | DP1 | KJ3 | IC2 | YT4 | Unavailable | |
5 | E | 49 | DP5 | KJ4 | IC3 | YT5 | Unavailable | |
6 | F | 25 | DP3 | KJ1 | IC4 | YT5 | Available | |
6 | F | DP4 | KJ2 | IC5 | YT3 | Available | ||
7 | G | DP4 | KJ3 | IC1 | YT5 | Available | ||
7 | G | DP2 | KJ4 | IC2 | YT3 | Available | ||
7 | G | DP3 | KJ5 | IC3 | YT4 | Available | ||
7 | G | DP4 | KJ4 | IC4 | YT4 | Available | ||
7 | G | 9 | DP1 | KJ2 | IC1 | YT2 | Available | |
8 | H | 30 | DP5 | KJ3 | IC2 | YT3 | Available | |
8 | H | 16 | DP3 | KJ4 | IC3 | YT4 | Available | |
8 | H | 43 | DP5 | KJ4 | IC4 | YT5 | Available | |
8 | H | 35 | DP3 | KJ5 | IC5 | YT1 | Available | |
8 | H | 14 | DP4 | KJ1 | IC4 | YT2 | Available | |
9 | I | DP4 | KJ2 | IC3 | YT3 | Unavailable | ||
9 | I | DP2 | KJ3 | IC4 | YT4 | Unavailable | ||
9 | I | DP3 | KJ4 | IC5 | YT1 | Unavailable | ||
9 | I | 28 | DP4 | KJ1 | IC5 | YT2 | Unavailable | |
9 | I | 46 | DP5 | KJ2 | IC3 | YT3 | Unavailable |
Below is the Dax I have tried:
Solved! Go to Solution.
@Jatin77 , I think you need use allselected, in case you want filter to work. Also prefer isinscope. refer my example
IsInScope - Switch Rank at different levels: https://youtu.be/kh0gezKICEM
@Jatin77 , I think you need use allselected, in case you want filter to work. Also prefer isinscope. refer my example
IsInScope - Switch Rank at different levels: https://youtu.be/kh0gezKICEM
Hi @amitchandak , Thank you for replying. I have used "all selected" in place of "all" and this resolved the issue. Thanks a lot!
User | Count |
---|---|
26 | |
12 | |
8 | |
8 | |
5 |
User | Count |
---|---|
30 | |
14 | |
12 | |
12 | |
7 |