Forum Discussion

bajimmy1983's avatar
bajimmy1983
Icon for Advocate V rankAdvocate V
9 years ago
Solved

Dynamic RANKS using CARDS elements

Hello Community, how are you?    Could you please help me? PBIX file I am using.   Each Client participates in one or more than one GLOBAL GROUP. There are 02: HAVAS CREATIVE and HAVAS MEDIA. Und...
General Comment
Need Help
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    9 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

bajimmy1983's avatar
bajimmy1983
Icon for Advocate V rankAdvocate V
9 years ago

Hi Anonymous, sorry to delay my reply. I am very busy these days :), but It will be helpfull if you can help me learn a little more.

 

I am attaching again PBD sample file and also Excel file (sample data base I have used) as you requested. 

 

Thanks a lot for your time and sorry again for my delay.

 

Jaderson (AKA Jimmy)

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi bajimmy1983,

 

Can you share them to onedrive ? Based on policy reason, I can't find the files at you shared link.

 

Regards,

Xiaoxin Sheng

  • bajimmy1983's avatar
    bajimmy1983
    Icon for Advocate V rankAdvocate V
    9 years ago

    Hi Anonymous,

     

    Sure I can. Please find both files at One Drive sample files.

     

    Thanks a lot again,

     

    Jaderson (Jimmy)

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      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

      • bajimmy1983's avatar
        bajimmy1983
        Icon for Advocate V rankAdvocate V
        9 years ago

        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 :)