Forum Discussion
Dynamic RANKS using CARDS elements
- Anonymous9 years ago
Hi bajimmy1983,
>>So, I have tried as you suggested, but the problem persists when I have all Slicers selected (last print below). Maybe I am doing something wrong yet.
After I modify the formula from your steps, it still works on my side:
Z_Rank Dynamic = IF(ISBLANK([Invest Pilot BRL]),BLANK(), IF(ISFILTERED(Pilot[GLOBAL GROUP])&&ISFILTERED(Pilot[GROUP]),//check if isfiltered global group and group at same time RANKX(ALL(Pilot[CLIENT]),[Invest Pilot BRL],,0,Dense),//client RANKX(ALLSELECTED(Pilot[CLIENT]),[Invest Pilot BRL],,0,Dense))//global )
Regards,
Xiaoxin Sheng
Hi bajimmy1983,
You can try to use below formula, it will auto switch the compare level based on group level slicer.
Z_Rank Dynamic = IF(ISBLANK([Tot Bil Pilot BRL]),BLANK(), IF(ISFILTERED(Pilot[GLOBAL GROUP])&&ISFILTERED(Pilot[GROUP]),//check if isfiltered global group and group at same time RANKX(ALL(Pilot[CLIENT]),[Tot Bil Pilot BRL],,0,Dense),//client RANKX(ALLSELECTED(Pilot[CLIENT]),[Tot Bil Pilot BRL],,0,Dense))//global )
Regards,
Xiaoxin Sheng
Hi Anonymous, sorry again for delaying my reply. So many things at same time.
So, I have tried as you suggested, but the problem persists when I have all Slicers selected (last print below). Maybe I am doing something wrong yet.
Let's take RB client as example: It must be 2nd in the overall rank (which is correct), but inside its Global Group (in this case HAVAS CREATIVE) Z_Rank Dynamic measure is ranking it as 2nd the same way as overall (which is incorrect).
The correct should be 2nd as overall and 1st inside Global Group (HAVAS CREATIVE) and Group (HWW) selections. I really tried to fix it, but it seems very difficult to me yet. Sorry! I will not give up. Of course there is a way to do so and sonner or later we will discover.
In your suggested measure, I just changed [Tot Bil Pilot BRL] for [Invest Pilot BRL] because the second is the right for our situation. The rest is the same.
Measure:
Z_Rank Dynamic =
IF (
ISBLANK ( [Invest Pilot BRL] );
BLANK ();
IF (
ISFILTERED ( Pilot[GLOBAL GROUP] ) && ISFILTERED ( Pilot[GROUP] );
//check if isfiltered global group and group at same time
RANKX (
ALL ( Pilot[CLIENT] );
[Invest Pilot BRL];
;
0;
DENSE
);
//client
RANKX ( ALLSELECTED ( Pilot[CLIENT] ); [Invest Pilot BRL];; 0; DENSE )
)
)Screens:
Correct
Correct
Correct
Wrong
Thanks again for your time :)
- Anonymous9 years agoNot applicable
Hi bajimmy1983,
>>So, I have tried as you suggested, but the problem persists when I have all Slicers selected (last print below). Maybe I am doing something wrong yet.
After I modify the formula from your steps, it still works on my side:
Z_Rank Dynamic = IF(ISBLANK([Invest Pilot BRL]),BLANK(), IF(ISFILTERED(Pilot[GLOBAL GROUP])&&ISFILTERED(Pilot[GROUP]),//check if isfiltered global group and group at same time RANKX(ALL(Pilot[CLIENT]),[Invest Pilot BRL],,0,Dense),//client RANKX(ALLSELECTED(Pilot[CLIENT]),[Invest Pilot BRL],,0,Dense))//global )
Regards,
Xiaoxin Sheng
- bajimmy19839 years agoAdvocate V
Anonymous. Hi, how are you? I am always late replying you, sorry.
So, thanks a lot for your commitment. I appreciate it very much.
I really cannot understand what is going on, because I am using same measure as yours, same database, same filters and my result are different. :smileyfrustrated: :smileysad:
See what I get:
Result from 24, July, 2017
Anyway, thanks a lot again and I will try to find the gap, but I cannot figure it out now.
Best regards,
Jimmy