Forum Discussion

mitchemmonkey's avatar
mitchemmonkey
Frequent Visitor
3 years ago
Solved

SUB RANKX HELP

I have the following table called SUMMARISE2, which I have ranked according to TEST2, called TESTRANK2. For each test name (i.e., TEST2/TESTRANK2) I then want to sub rank the rows based on value in "...
v-easonf-msft's avatar
v-easonf-msft
Community Support
3 years ago

Hi， mitchemmonkey 

These two rankings are independent of each other and don't seem to be that complicated.

I think the following two formulas will work.

Calculated columns:

TESTRANK2 = RANKX('Table','Table'[TETS2],,DESC,Dense)
Rank = RANKX(FILTER('Table','Table'[TETS2]=EARLIER('Table'[TETS2])),'Table'[LOT NO2],,DESC,Dense)

Other Related threads:

https://www.sqlbi.com/articles/rankx-on-multiple-columns-with-dax-and-power-bi/ 

 

Best Regards,
Community Support Team _ Eason

mitchemmonkey's avatar
mitchemmonkey
Frequent Visitor
3 years ago

v-easonf-msft 

One more question. How do I change the DAX to reurnthe highest rank for each category:, i.e.,:

 

 

  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support
    3 years ago

    Hi， mitchemmonkey 

    Try this:

    Max rank = CALCULATE(MAX('Table'[Rank]),ALLEXCEPT('Table','Table'[TETS2]))

    Best Regards,
    Community Support Team _ Eason