Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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. Under Global Group we can have Groups (BETC, HMG and HWW).
Desire outcome using cards: When I select a single Client, but do not select any Global Group nor Group, ranking will be considered as overall, comparing all Clients with each other. When I add a selection with Global Group ranking will drill down to this level and the same situation when I select Client + Global Group + Group.
Considering what I have in pbix file:
*** 1 Matrix results considering as example client "RB" (All correct):
*** 2 Slicer results selecting client "TIM" and using both "Z_Rnk_Individual" and "Z_Rnk_Overall" measures (Wrong😞
Measures:
Z_Rnk_Individual = IF ( ISBLANK ( [Tot Bil Pilot BRL] ); BLANK (); RANKX ( ALL ( Pilot[CLIENT] ); CALCULATE ( [Tot Bil Pilot BRL]; ALLSELECTED ( Pilot[CLIENT] ) ); ; 0; DENSE ) )
Z_Rnk_Overall = IF ( ISBLANK ( [Tot Bil Pilot BRL] ); BLANK (); RANKX ( ALL ( Pilot[CLIENT] ); [Tot Bil Pilot BRL];; 0; DENSE ) )
Thank you so much in advance for your time and support. I hope you can help me to keep moving forward in this journey 🙂
Solved! Go to Solution.
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,
Can you please share some sample data to test? I can't download the pbix file which you shared.
Regards,
Xiaoxin Sheng
Hi @v-shex-msft, 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)
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
Hi @v-shex-msft,
Sure I can. Please find both files at One Drive sample files.
Thanks a lot again,
Jaderson (Jimmy)
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 @v-shex-msft, 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:
Thanks again for your time 🙂
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
@v-shex-msft. 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.
See what I get:
Anyway, thanks a lot again and I will try to find the gap, but I cannot figure it out now.
Best regards,
Jimmy
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
96 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |