Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

RANKX problem

I have a very simple table to test out the RANKX function shown below

 

countriesvalue
Japan3456234
china23452345
france6543
usa12346
sinagpore54322

 

i'm trying to create a new column called rank, and in the column header i used the RANKX code as follow:

 

Rank = RANKX (ALL(Sheet1[countries]),Sheet1[value])
 
But the rank column returns 1 for every value. However ,when i use 
 
Rank = RANKX (ALL(Sheet1),Sheet1[value])
 
the rank column works as intended. However when i have more data and needs the rank for a specific column i wouldn't be able to do this anymore, so i'm wondering why the first formula doesn't work? Thank you.
Need Help
  • v-xicai's avatar
    v-xicai
    7 years ago

    Hi Anonymous ,

     

    The syntax of RANKX function is like this format :RANKX(<table>, <expression>[, <value>[, <order>[, <ties>]]]) ,the first argument is Table but not a Filed. You can go through the link https://docs.microsoft.com/zh-cn/dax/rankx-function-dax to learn more about the RANKX .

     

    If you need to rank the value field for every countries categories, then try to create column in DAX below.

    Rank= RANKX(FILTER(Sheet1,Sheet1[countries]=EARLIER(Sheet1[countries])),Sheet1[value],,ASC,Dense)

     

    Best Regards,

    Amy

     

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

4 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    7 years ago

    Anonymous 

     

    You need a calculated column? Not a measure...right?

     

    Try this as column

     

    Rank =
RANKX (
    ALLSELECTED ( Sheet1[countries] ),
    CALCULATE (
        SUM ( Sheet1[value] ),
        FILTER ( Sheet1, Sheet1[countries] = EARLIER ( Sheet1[countries] ) )
    )
)
  • v-xicai's avatar
    v-xicai
    Community Support
    7 years ago

    Hi Anonymous ,

     

    The syntax of RANKX function is like this format :RANKX(<table>, <expression>[, <value>[, <order>[, <ties>]]]) ,the first argument is Table but not a Filed. You can go through the link https://docs.microsoft.com/zh-cn/dax/rankx-function-dax to learn more about the RANKX .

     

    If you need to rank the value field for every countries categories, then try to create column in DAX below.

    Rank= RANKX(FILTER(Sheet1,Sheet1[countries]=EARLIER(Sheet1[countries])),Sheet1[value],,ASC,Dense)

     

    Best Regards,

    Amy

     

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

  • v-xicai's avatar
    v-xicai
    Community Support
    7 years ago

    Hi  Anonymous ,

     

    Does that make sense? If so, kindly mark my answer as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

     

    Best regards

    Amy

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thank you for your explanation Amy, it was helpful.