Forum Discussion

villa1980's avatar
villa1980
Resolver II
1 year ago
Solved

Rank Returning Blank

Hi all, I must be missing something very simple here...
I have 2 tables, One that lists Depots by name and depot number, the second is a list of scores by Depot Name and Number
A seperate table has been created to store a number of metrics relating to other items.
The Depot List and Score are joined by Depot number.
When I come to rank the Depot it does work, however, there is a blank in Depot Name which was not there before and is given a rank which is throwing it off...formaule are below how I get to it and an example of what is returned.

Depot Name  Score
Carlisle            10
Derby               2
Bangor             5

Score = sum(Depot_Score[Score])
Rank_Score = 
RANK(DENSE,ALLSELECTED(Depot[Depot Name]), ORDERBY([Score],desc))
 
When the Rank is included then this happens
Depot Name  Score Rank
                                  4
Carlisle            10       1
Derby               2        3
Bangor             5        2
 
Any light to be shed on this would be great.
Thanks
Alex
Need Help
  • bhanu_gautam's avatar
    bhanu_gautam
    1 year ago

    villa1980 , Try using the below measure

     

    Rank_Score =
    RANKX(
    FILTER(
    ALLSELECTED(Depot[Depot Name]),
    NOT(ISBLANK(Depot[Depot Name]))
    ),
    [Score],
    ,
    DESC,
    DENSE
    )

3 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

    villa1980 , Try using the below measure

     

    Rank_Score =
    RANKX(
    FILTER(
    ALLSELECTED(Depot[Depot Name]),
    NOT(ISBLANK(Depot[Depot Name]))
    ),
    [Score],
    ,
    DESC,
    DENSE
    )

    • villa1980's avatar
      villa1980
      Resolver II
      1 year ago

      Thank-you for the quick reply, unfortunately it has not removed the blank Depot Name, I wonder if there is an issue with my join between the 2 tables and there is a rogue depot that is causing the blank.

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi villa1980 ,

     

    First thanks for the quick reply from bhanu_gautam , I have some other thoughts to add:

    Please try using the below measure:

    Rank_Score =
RANKX(
FILTER(
ALLSELECTED(Depot[Depot Name]),
Depot[Depot Name]<>BLANK()
),
[Score],
,
DESC,
DENSE
)

    If that doesn't work, please provide some example data and table relationships (to protect your private data).

     

    Best Regards,

    Neeko Tang

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