Forum Discussion
Ranking rows based multiple columns with a specific order
Hi,
I need to rank my data based on updatedDate(Sort Asc) and Status but only increment for specific status. For most part i am able to use rankx and get an order but need help on modifying this to ensure it increments only for certain status.
I.e. For each ID, Status "ND" will always be version 1 (Updated date helps in ensuring this) and from there on only when status is "NDV" the version needs to be incremented. This change of status between "ND", intermediate status (C, E) and "NDV" can happen any number of times. ND will occur only once.
My current DAX for column "version" below
Version = RANKX( FILTER('TestVersion', 'TestVersion'[Id]=EARLIER('TestVersion'[Id])),'Test Version'[UpdatedDate] && 'Test Version'[Status]= "NDV",,ASC,Dense)
Output needed: "VersionNeeded" column below
Sample data and out put needed
| Id | Status | UpdatedDate | UpdatedDateOrder | Version | VersionNeeded |
| 1 | ND | 1-Jan-21 | 1 | 1 | 1 |
| 1 | E | 1-Feb-21 | 2 | 1 | 1 |
| 1 | NDV | 1-Mar-21 | 3 | 2 | 2 |
| 1 | C | 1-Apr-21 | 4 | 1 | 2 |
| 1 | NDV | 1-May-21 | 5 | 2 | 3 |
| 1 | E | 1-Jun-21 | 6 | 1 | 3 |
| 1 | NDV | 1-Jul-21 | 7 | 2 | 4 |
| 1 | E | 1-Aug-21 | 8 | 1 | 4 |
| 1 | NDV | 1-Sep-21 | 9 | 2 | 5 |
| 2 | ND | 1-Jan-21 | 1 | 1 | 1 |
| 2 | E | 1-Feb-21 | 2 | 1 | 1 |
| 2 | NDV | 1-Mar-21 | 3 | 2 | 2 |
| 2 | E | 1-Apr-21 | 4 | 1 | 2 |
| 2 | NDV | 1-May-21 | 5 | 2 | 3 |
| 2 | C | 1-Jun-21 | 6 | 1 | 3 |
| 2 | NDV | 1-Jul-21 | 7 | 2 | 4 |
Thanks in advance for any help!