topic Re: SUB RANKX HELP in DAX Commands and Tips

SUB RANKX HELP

mitchemmonkey — Tue, 27 Sep 2022 15:40:02 GMT

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

Re: SUB RANKX HELP

v-easonf-msft — Wed, 28 Sep 2022 10:25:42 GMT

Hi , mitchemmonkey

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

Best Regards,
Community Support Team _ Eason

Re: SUB RANKX HELP

mitchemmonkey — Mon, 03 Oct 2022 07:40:35 GMT

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.

Re: SUB RANKX HELP

v-easonf-msft — Mon, 03 Oct 2022 08:20:22 GMT

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

Re: SUB RANKX HELP

mitchemmonkey — Mon, 03 Oct 2022 09:00:00 GMT

v-easonf-msft Perfect - thank you!

Re: SUB RANKX HELP

mitchemmonkey — Tue, 04 Oct 2022 07:40:18 GMT

v-easonf-msft

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

Re: SUB RANKX HELP

v-easonf-msft — Tue, 04 Oct 2022 09:18:01 GMT

Hi， mitchemmonkey

Try this:

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

Best Regards,
Community Support Team _ Eason