Forum Discussion
Using function Rank or Rankx for grouped rankings
Hello,
I have two tables related to each other by ID. One table has information about dates when a equipment failed and how it did and the other table contains information about all equipments (id,category, room, building) just like this:
| Eq ID | Month year | Failure Mode |
| 001 | 2-2024 | - |
| 002 | 3-2024 | - |
002 | 6-2024 | - |
| 004 | 8-2024 | - |
| 003 | 2-2024 | - |
| Eq ID | Cathegory | Room | Building |
| 001 | Electrical | 10 | A |
| 002 | Electrical | 11 | A |
| 003 | Mechanical | 10 | A |
| 004 | Mechanical | 12 | B |
I already created a metric that counts how many times an equipment appears in table Failures:
| Eq ID | Cathegory | Room | Total Failures | Rank per Cathegory | Rank per Room |
| 001 | Electrical | 10 | 1 | 2 | 1 |
| 002 | Electrical | 11 | 2 | 1 | 1 |
| 003 | Mechanical | 10 | 1 | 1 | 1 |
| 004 | Mechanical | 12 | 1 | 1 | 1 |
NOTE: I would like to keep using the metric Count_failures because it can changes over time instead of a calculated column in the table Equipments wich will have an static value.
3 Replies
Hi SpanishEnjoyer -Create a new measure that ranks the equipment based on the number of failures within its respective category
Rank_Per_Category =
RANKX(
ALLSELECTED('Table Equipments'[ID Eq]),
[Count_Failures],
,
DESC,
DENSE
)create another measure for rank for each room as below:
Rank_Per_Room =
RANKX(
ALLSELECTED('Table Equipments'[ID Eq]),
[Count_Failures],
,
DESC,
DENSE
)this way you can achieve, hope it works.
Thanks for your answer, but the new metrics works as the metric Ranking that is already created. It doesn´t rank the equipment by cathegory or room.
Hi SpanishEnjoyer ,
I don't quite understand your grouping logic, if the room ordering 001 and 003 should be the same, they should both return 2.
Please detail your needs and demonstrate the expected results.
Best regards,
Community Support Team_ Scott Chang
