Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
---|---|
42 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |