SUB RANKX HELP

[mitchemmonkey]

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

[v-easonf-msft · Accepted solution]

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

[v-easonf-msft]

Hi , @mitchemmonkey 

Can you share a simplified data table and expected results for further research ?

Best Regards,
Community Support Team _ Eason

[mitchemmonkey]

@v-easonf-msft 

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.

[v-easonf-msft · Accepted solution]

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]

@v-easonf-msft 

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

[v-easonf-msft · Accepted solution]

Hi， @mitchemmonkey 

Try this:

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

Best Regards,
Community Support Team _ Eason

[mitchemmonkey]

@v-easonf-msft Perfect - thank you!