Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
Hi, @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 _ Eason
One more question. How do I change the DAX to reurnthe highest rank for each category:, i.e.,:
Hi, @mitchemmonkey
Try this:
Max rank = CALCULATE(MAX('Table'[Rank]),ALLEXCEPT('Table','Table'[TETS2]))
Best Regards,
Community Support Team _ Eason
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |