Forum Discussion
How to Create an column that creates an ID# based on multiple criteria and last instances?
Hi,
I'm relatively new to Power BI, and I'm trying to do something that I'm not even sure is possible.
I have a table that is similar to the one below. I am trying to create an ID number for "events". The events always follow the same process in the status column:
Step 1 - Engineer Required
Step 2 - Engineer on machine
Step 3 - Repair Complete
They are in date order, across multiple machines. Is there a way of creating a unique value for each "event"?
| Machine | Status | DateTime Raised |
| Machine 1 | Repair Complete | 25/03/2022 15:04 |
| Machine 1 | Engineer on machine | 25/03/2022 15:03 |
| Machine 1 | Engineer Required | 25/03/2022 14:57 |
| Machine 2 | Repair Complete | 25/03/2022 14:52 |
| Machine 2 | Engineer on machine | 25/03/2022 14:52 |
| Machine 1 | Repair Complete | 25/03/2022 14:51 |
| Machine 2 | Engineer Required | 25/03/2022 14:51 |
| Machine 1 | Engineer on machine | 25/03/2022 14:51 |
| Machine 2 | Repair Complete | 25/03/2022 14:46 |
| Machine 2 | Engineer on machine | 25/03/2022 14:38 |
| Machine 1 | Engineer Required | 25/03/2022 14:36 |
| Machine 2 | Engineer Required | 25/03/2022 13:51 |
3 Replies
- amitchandak
Super User
TommyWommy . Do you need a unique number in each row ?
For that, you can add an index column in Power Query
Power Query- Index Column: https://youtu.be/NS4esnCDqVw
Another option is have column Rank in DAX, that is based on a combination of columns
Column Rank: https://www.youtube.com/watch?v=wDS_Vi4r9I4&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=39
For Rank
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columnsNo, I do not need a unique number in each row.
I would like to find all instances of "engineered required" for any given machine. Then find the next instance of "engineer on machine" and then "repair complete". Then group that together as 1. But apply a unique value everytime it happens.
TommyWommy , Did you end up finding a solution to this? I am trying to solve the same problem.
