Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hello,
I am unable to get the proper ranking on a simple dataset. I have Date, Country and Projection values, please find below screenshot:
Please help me get the dynamic ranking based on the date filter selection.
Solved! Go to Solution.
Hi @vin26 ,
Sorry for late back.
Please try to create a date table and use its column as slicer. I modified the formula, please try again.
Date = CALENDAR(MIN(country_projection[date]),MAX(country_projection[date]))
Measure 2 =
RANKX (
ALLSELECTED ( country_projection ),
CALCULATE (
SUM ( country_projection[projection] ),
FILTER (
ALLEXCEPT ( country_projection, country_projection[country] ),
country_projection[date] >= MIN ( 'Date'[Date] )
&& country_projection[date] <= MAX ( 'Date'[Date] )
)
),
,
DESC,
DENSE
)
Hi @v-xuding-msft Thanks a lot for the solution, this works fine. Excellent!
I have also got another logic which also works for my requirement:
SumCountry = SUM(country_projection[projection])
RankCountry = IF(
ISFILTERED(country_projection[date]),
RANKX(
ALLSELECTED(country_projection[country]),[SumCountry],,DESC,Skip
)
)
Hi @vin26 ,
Please try this:
Measure 2 =
RANKX (
ALLSELECTED ( country_projection ),
CALCULATE (
SUM ( country_projection[projection] ),
ALLEXCEPT ( country_projection, country_projection[country] )
),
,
DESC,
DENSE
)
Hi @v-xuding-msft Thanks for the suggestion, it works for the small data source, when I load the actual data (size 30k rows) and selecting date range, rank is not showing correctly. But works without any date selection.
with date range selection
selecting all the date selection:
Hi @vin26 ,
Sorry for late back.
Please try to create a date table and use its column as slicer. I modified the formula, please try again.
Date = CALENDAR(MIN(country_projection[date]),MAX(country_projection[date]))
Measure 2 =
RANKX (
ALLSELECTED ( country_projection ),
CALCULATE (
SUM ( country_projection[projection] ),
FILTER (
ALLEXCEPT ( country_projection, country_projection[country] ),
country_projection[date] >= MIN ( 'Date'[Date] )
&& country_projection[date] <= MAX ( 'Date'[Date] )
)
),
,
DESC,
DENSE
)
Hi @v-xuding-msft Thanks a lot for the solution, this works fine. Excellent!
I have also got another logic which also works for my requirement:
SumCountry = SUM(country_projection[projection])
RankCountry = IF(
ISFILTERED(country_projection[date]),
RANKX(
ALLSELECTED(country_projection[country]),[SumCountry],,DESC,Skip
)
)
If in doubt, check out what Marco & Alberto say
https://www.daxpatterns.com/ranking/
Proud to be a Super User!
@amitchandak @harshnathani @Daviejoe @Pragati11 thank you everyone for the quick response. Tried all the suggestions but still not working. I have tried below measure with 'ALLEXCEPT, it somewhat works, but ranking is still not correct!!
I have only 3 in source data columns, date, country and projection. Please let me know if you have any suggestions.
Hi @vin26 ,
Share some sample data in text format.
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
Hi @vin26 ,
Can you share the screenshot on when you tried our method of using ALLSELECTED and what's the output you get?
Did you try this:
Rank = RANKX(ALLSELECTED(country_projection), CALCULATE(SUM(country_projection[projection])),, DESC, Dense)
Thanks,
Pragati
HI @vin26 ,
The scresnhot just doen't help me with no description on why it is wrong.
Why do you think it is wrong? What you are expecting as output when you filter on date?
Thanks,
Pragati
Hi @Pragati11 in the screenshot attached 'Projections' are sorted in descending order, my expectation is to get ranking in 1,2,3,4,... order, but if you see the measure 'Rank' shows 1,1,1,1,1...
@Pragati11 Please find the sample file in below link, there are 30K rows of data, I haved reduced it to 1k
https://www.dropbox.com/s/qphhj10lu5qjta7/Ranking.pbix?dl=0
@vin26 , Rank is always calculated at level what is lowest .
in this case try
rankx(all(Table[country]),calculate(sum(table[projection])),desc,dense)
For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...
HI @vin26 ,
Try this measure
Measure = RANKX(ALLSELECTED( Country_projection[Country], Country_projection[projection]), CALCULATE(SUM(Country[projection)),,DESC)
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
Rank =
RANKX (
ALLSELECTED (
CALCULATE ( SUM (country_projection[country projection])),
DESC)
Proud to be a Super User!
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
92 | |
87 | |
84 | |
65 | |
49 |
User | Count |
---|---|
140 | |
114 | |
110 | |
59 | |
59 |