Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get 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

Reply
vin26
Resolver I
Resolver I

Getting wrong Ranking on date filter

Hello,

 

I am unable to get the proper ranking on a simple dataset. I have Date, Country and Projection values, please find below screenshot:

rank_issue.JPG

 

Please help me get the dynamic ranking based on the date filter selection.

 

2 ACCEPTED SOLUTIONS

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
)

v-xuding-msft_0-1598344678367.png

 

 

 

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

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

 

View solution in original post

17 REPLIES 17
v-xuding-msft
Community Support
Community Support

Hi @vin26 ,

 

Please try this:

Measure 2 =
RANKX (
    ALLSELECTED ( country_projection ),
    CALCULATE (
        SUM ( country_projection[projection] ),
        ALLEXCEPT ( country_projection, country_projection[country] )
    ),
    ,
    DESC,
    DENSE
)

v-xuding-msft_0-1597903516244.png

 

 

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

Ranking3.JPG

 

selecting all the date selection:

ranking4.JPG

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
)

v-xuding-msft_0-1598344678367.png

 

 

 

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

 

Daviejoe
Memorable Member
Memorable Member

If in doubt, check out what Marco & Alberto say

 

https://www.daxpatterns.com/ranking/





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




vin26
Resolver I
Resolver I

@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!!

 

ranking.JPG

 

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

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

@Pragati11 Hi Pragati, here it is

ranking2.JPG

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

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

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... 

 

ranking2.JPG

Hi @vin26 ,

 

Share some sample data, so that we can try replicating your issue.

 

Thanks,

Pragati

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

@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

 

amitchandak
Super User
Super User

@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...

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
harshnathani
Community Champion
Community Champion

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)

Daviejoe
Memorable Member
Memorable Member

Rank =

RANKX (

    ALLSELECTED (

      CALCULATE  ( SUM (country_projection[country projection])),

         DESC)

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Pragati11
Super User
Super User

Hi @vin26 ,

 

Try using ALLSELECTED in your DAX in place of ALL.

 

Thanks,

Pragati

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.