How to Create an column that creates an ID# based on multiple criteria and last instances?

[TommyWommy]

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

[amitchandak]

@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-columns

[TommyWommy]

No, 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.