Forum Discussion

sthomas38's avatar
sthomas38
Frequent Visitor
4 years ago
Solved

Rank with no duplicates

Hi,

I am trying to achieve this ranking (no duplicates) - please see below snapshot. I have to rank each Attribute for around 1000 MSAs. 

I tried adding a random value to the rank - but that is not giving a single digit value between 1 and 11 and giving me tripple digit values. 

 

 

Let me know if you have any questions please. Thank you so much for your time and help.

Need Help

4 Replies

    • sthomas38's avatar
      sthomas38
      Frequent Visitor
      4 years ago

      Hi Tahreem,

      I am not sure if I did it right, or if the solution was not right for this instance - 

       

      This is what I am getting after adding index column in Query Editor and then a RANKX Column. 

      VALUE for me is a Calculated Column - in my original dataset, I have IDs thats are distinctly count that created this VALUE column for me. Not sure if that helps. Even to get the RANK column - I had to create it as a Measure and not Column.

       

  • v-luwang-msft's avatar
    v-luwang-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi sthomas38 ,

    First add index:

    Close and apply,then use the following dax to create a column:

    RANK1 = RANKX(FILTER('Table','Table'[MSA]=EARLIER('Table'[MSA])),'Table'[Value_count]+0.000001*'Table'[Index],,DESC,Dense)

    Output result:

     

    And you could refer my blog about rank with  same values:

    https://community.powerbi.com/t5/Community-Blog/Sort-when-with-same-value/ba-p/2012332 

     

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


    Best Regards

    Lucien

     

    • sthomas38's avatar
      sthomas38
      Frequent Visitor
      4 years ago

      Hi!

      Thank you so much for this solution. It helped me! Because of being able to create rank as a column (and nor measure), I was able to achieve so much more (calling out the 3rd rank network and 5th rank network) etc. 

      Appreciate your help. Thank you.