Forum Discussion

vin26's avatar
vin26
Resolver I
6 years ago
Solved

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:

 

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

 

  • v-xuding-msft's avatar
    v-xuding-msft
    6 years ago

    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
)

     

     

     

  • vin26's avatar
    vin26
    6 years ago

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

     

17 Replies

  • Pragati11's avatar
    Pragati11
    Super User
    6 years ago

    Hi vin26 ,

     

    Try using ALLSELECTED in your DAX in place of ALL.

     

    Thanks,

    Pragati

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
    Daviejoe
    Memorable Member
    6 years ago

    Rank =

    RANKX (

        ALLSELECTED (

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

             DESC)

     

     

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    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/367415

  • vin26's avatar
    vin26
    Resolver I
    6 years ago

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

    • Pragati11's avatar
      Pragati11
      Super User
      6 years ago

      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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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)

  • v-xuding-msft's avatar
    v-xuding-msft
    Community Support
    6 years ago

    Hi vin26 ,

     

    Please try this:

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

     

     

    • vin26's avatar
      vin26
      Resolver I
      6 years ago

      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:

      • v-xuding-msft's avatar
        v-xuding-msft
        Community Support
        6 years ago

        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
)