Forum Discussion
Add a Group Number column
Hi,
I am trying to create a new column in Power BI that will allow me to group my data by a subset of the entire table. Here is what I am trying to accomplish.
| Event Number | Unit | Status | Group Number |
| 1234 | Truck | DP | 1 |
| 1234 | Car | DP | 2 |
| 1234 | Truck | AM | 1 |
| 1234 | Car | AM | 2 |
| 1234 | Truck | DP | 3 |
| 5522 | Truck | DP | 4 |
The Group Number column is what I need to create and is based on the following:
1. Group by Event Number and Unit
2. The Group Number is autogenterated
3. The Group Number starts when the status is DP end then restarts at the next DP within the same Event Number and Unit group
How can I do this?
Hi amulder,
To achieve your requirement, create two calculate columns using DAX as below:
Only Rank By DP = RANKX(FILTER(Table1, Table1[Status] = EARLIER(Table1[Status])), RANKX(ALL(Table1), Table1[Index]), , DESC, Dense)
Final Rank = IF(Table1[Status] = "DP", Table1[Only Rank By DP], CALCULATE(MIN(Table1[Only Rank By DP]), FILTER(Table1, Table1[Event Number] = EARLIER(Table1[Event Number]) && Table1[Unit] = EARLIER(Table1[Unit]) && Table1[Status] = "DP")))
Regards,
Jimmy Tao
1 Reply
Hi amulder,
To achieve your requirement, create two calculate columns using DAX as below:
Only Rank By DP = RANKX(FILTER(Table1, Table1[Status] = EARLIER(Table1[Status])), RANKX(ALL(Table1), Table1[Index]), , DESC, Dense)
Final Rank = IF(Table1[Status] = "DP", Table1[Only Rank By DP], CALCULATE(MIN(Table1[Only Rank By DP]), FILTER(Table1, Table1[Event Number] = EARLIER(Table1[Event Number]) && Table1[Unit] = EARLIER(Table1[Unit]) && Table1[Status] = "DP")))
Regards,
Jimmy Tao