Forum Discussion
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 "Lot No2", so that for each TEST2 category, I have a rank of 1,2,3 (and so on), which repeats for each test name it comes across. Note - The actual number currently in "Lot No2" is irrelevant and I just need to assign a 1,2,3 etc number to each independent variable in the group, and not specifically rank based on the actual value.
Any help on the correct DAX expression would be appreciated
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
6 Replies
Hi , mitchemmonkey
Can you share a simplified data table and expected results for further research ?
Best Regards,
Community Support Team _ Easonthanks 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 _ Eason
