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 everyone,
I have a question regarding RANKX(). My report consists of a leaderboard ranking different Districts based on a calculated measure (YoY Growth in %). The tricky thing is that I must "place" each District under its respective Region. You have North America, South America, etc. Also, the top three Districts have their own box at the top.
Now however, my solution creates the following issues:
On this table, you see the results of my RANKX() formula, which doesn't work.
Districts with "OTHER" in their names are filtered out from the ranking (but must be kept in the data model as their values add up to Regional level), and I think they are the cause of the problem.
Currently, my RANKX() formula is this one :
Rank = if(hasonevalue(Local[DISTRICT]), calculate(rankx(FILTER(all(Local),Local[Custom]=0),FPins[PINS YTD YoY Δ]), ALLEXCEPT(Local,Local[DISTRICT])), "-")
Local is a table with District, Region and Global Region Data.
The Local[Custom]=0 is a way for me to flag "OTHER" districts (they have the value 1 so I can exclude them).
FPins in the big table with historical data, and PINS YTD YoY Δ is a measure of YTD Growth.
I use the if statement in order to display "-" when, on another page, somebody selects more than one District.
Currently, the model looks like so.
(The 3P part is an oddity that I must keep to calculate an aggregate number, but it has no link whatsoever in the Ranking. At the moment, the relations are inactive, but they are set to active when the model is running. I disabled them for testing at the moment.)
So in summary, my question is, what variation of RANKX() (or maybe EARLIER()) can I use in order to run a ranking on a sample of my Districts (exclusion of OTHERS), but also having the RANKX() stay valid when I filter out the Districts by Leader Boxes and Regional Boxes.
Thanks a lot for your help and for reading up to here.
Solved! Go to Solution.
You may modify your measure using dax below and check if it can meet your requirement:
Rank = IF ( HASONEVALUE ( Local[DISTRICT] ), RANKX ( FILTER ( Local, Local[Custom] = 0 && Local[DDISTRICT] = EARLIER ( Local[DISTRICT] ) ), FPins[PINS YTD YoY Δ] ), "-" )
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
You may modify your measure using dax below and check if it can meet your requirement:
Rank = IF ( HASONEVALUE ( Local[DISTRICT] ), RANKX ( FILTER ( Local, Local[Custom] = 0 && Local[DDISTRICT] = EARLIER ( Local[DISTRICT] ) ), FPins[PINS YTD YoY Δ] ), "-" )
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 |
---|---|
113 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |