Forum Discussion
SUB RANKX HELP
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 _ EasonHi， mitchemmonkey
Try this:
Max rank = CALCULATE(MAX('Table'[Rank]),ALLEXCEPT('Table','Table'[TETS2]))Best Regards,
Community Support Team _ Eason
Hi , mitchemmonkey
Can you share a simplified data table and expected results for further research ?
Best Regards,
Community Support Team _ Eason
thanks for your reply - I'm pretty new to power BI so not sure what you refer to when asking for a simplified table, but I have mocked up in Excel what I want to acheieve:
Essentially, I need to rank within each original ranked category (i.e., 1st rank on the test name [which ive done under "TESTRANK""column], but then rank within each test name based on different text within the Lot No2 column [Rank column in excel mock up picture])
I dont want to use the Power query/transform option, and hoping for a DAX solution 🙂
Thank you for your time.
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 _ Easonv-easonf-msft Perfect - thank you!
