Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

RankX filter includes values with no data

Hi Folks,

I'm using a RankX function and a slicer in my report. 

 

Slicer is to view Data of particular Country and Year. RankX function for the Rank of increase in population for countries.


RankX function is working fine when i have data for all the countries.
When i change the year (let suppose 2020, and data for some countries is missing) so it gives me rank skipped for those countries.


 But when i select these two countries manually from the slicer then the data is correct.

Thanks for your help in advance.

Gaurav Jangra

 

 

 

Need Help
  • v-janeyg-msft's avatar
    v-janeyg-msft
    5 years ago

    Hi, Anonymous 

     

    It’s my pleasure to answer for you.

    According to your description,I think your formula don't consider 'year'.You can try to change your formula.

    Like this:

    Rank_Population_Inc% =
IF (
    NOT ( ISBLANK ( [Population_Inc%] ) ),
    RANKX (
        ALLSELECTED ( V_PopulationData),
        CALCULATE ( [Population_Inc%] ),
        ,
        ASC,
        SKIP
    )
)

    If it doesn’t solve your problem, please feel free to ask me.

     

    Best Regards

    Janey Guo

     

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

3 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi amitchandak 

      I am using Skip because i have to skip the rank for matching values.
      Also tried the Dense but not gives me the solution.

      the formula for the Rank Function is 
      Rank_Population_Inc% = IF(NOT(ISBLANK([Population_Inc%])), RANKX( ALLSELECTED( V_PopulationData[Country_Name] ) , CALCULATE( [Population_Inc%]),,ASC,Skip ))

  • v-janeyg-msft's avatar
    v-janeyg-msft
    Community Support
    5 years ago

    Hi, Anonymous 

     

    It’s my pleasure to answer for you.

    According to your description,I think your formula don't consider 'year'.You can try to change your formula.

    Like this:

    Rank_Population_Inc% =
IF (
    NOT ( ISBLANK ( [Population_Inc%] ) ),
    RANKX (
        ALLSELECTED ( V_PopulationData),
        CALCULATE ( [Population_Inc%] ),
        ,
        ASC,
        SKIP
    )
)

    If it doesn’t solve your problem, please feel free to ask me.

     

    Best Regards

    Janey Guo

     

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