cancel
Showing results for 
Search instead for 
Did you mean: 
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.

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

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


My Blog: Data Vibe
Connect: LinkedIn
Connect: Twitter






If this helps, Appreciate a KUDOS!
Did I answer your question? Mark my post as a solution!


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


My Blog: Data Vibe
Connect: LinkedIn
Connect: Twitter






If this helps, Appreciate a KUDOS!
Did I answer your question? Mark my post as a solution!


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


My Blog: Data Vibe
Connect: LinkedIn
Connect: Twitter






If this helps, Appreciate a KUDOS!
Did I answer your question? Mark my post as a solution!


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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
harshnathani
Super User
Super User

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


My Blog: Data Vibe
Connect: LinkedIn
Connect: Twitter






If this helps, Appreciate a KUDOS!
Did I answer your question? Mark my post as a solution!


Proud to be a Super User!




Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors