Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
So as many of you will know I have a beautiful ranking measure which ranks average months active by group:
Rank: Months Active = RANKX(ALLSELECTED('ELIGIBLE_POLICY'[GROUP POLICY NAME]),CALCULATE(SUM('ELIGIBLE_POLICY_HH'[AVERAGE_MONTHS ACTIVE])))
So this is super when in an unfiltered table, but when I filter the table on GROUP POLICY NAME so I can see just one group, the rank measure changes to 1.
I know why this is happenening, but I don't what it to. I want to be able to see their rank value against all groups, irrespective of whether the data has been sliced.
Help!
Solved! Go to Solution.
Hi @Anonymous ,
What happens when you modify your DAX as follows:
Rank: Months Active = RANKX(ALL('ELIGIBLE_POLICY'),CALCULATE(SUM('ELIGIBLE_POLICY_HH'[AVERAGE_MONTHS ACTIVE])))
Hi @Anonymous ,
Try chnging your DAX to as follows:
Rank: Months Active = RANKX(ALL('ELIGIBLE_POLICY'[GROUP POLICY NAME]),CALCULATE(SUM('ELIGIBLE_POLICY_HH'[AVERAGE_MONTHS ACTIVE])))
Let me know how this shows the output.
Thanks,
Pragati
Thanks @Pragati11 - this works beautifully if I only apply a slicer against the field [GROUP POLICY NAME] but each group has a reference or two associated to it, which people can also use to slice the data.
When I slice by Group reference instead of the GROUP NAME, the ranking goes to 1 still.
Is there any way around this?
Hi @Anonymous ,
What happens when you modify your DAX as follows:
Rank: Months Active = RANKX(ALL('ELIGIBLE_POLICY'),CALCULATE(SUM('ELIGIBLE_POLICY_HH'[AVERAGE_MONTHS ACTIVE])))
@Pragati11 it's no longer affected by the filter but it's not ranking 1 - 100, it's got some odd values, who would have been rank 1 is now rank 20, rank 2 then jumps to 317.
User | Count |
---|---|
97 | |
87 | |
78 | |
74 | |
69 |
User | Count |
---|---|
113 | |
105 | |
84 | |
65 | |
64 |