Create an "Event ID" based on area, start/end time and type of event

[Byte_me]

Dear PowerBi Community,

I'm looking for a function to create an event ID based on the following data structure.

Area - defines the place the event occurred. There can be multiple areas.
StartTime - defines the time at which the event started
EndTime - defines the time at which the event finished.
Event type- descriptive information about the type of an event
Event ID - the column that I want to create.

The problem statement:
Our current database records events, however, due to the functionality of the system one event will be broken down into a couple of events, and in the database, I need a way to "combine" them into one by creating an "event ID".

The tricky part is that the "end time" of the first event can be different by around 1-2 seconds from the start time of the next event due to application internal processing "lag". Or sometimes there will be a small event recorded in between the main ones that will last a couple of seconds, but it is a "false event"

Area	StartTime	EndTime	Event type	Event ID
L16	1/5/2021 6:21	1/5/2021 6:22	PDL-Changeover time	1
L16	1/5/2021 6:22	1/5/2021 9:51	PDL-Changeover time	1
L16	1/5/2021 9:52	1/5/2021 9:53	MPL-Minor Stoppages-Minor Stoppages-Safety System Fault	2
L16	1/5/2021 9:51	1/5/2021 9:53	MPL-Minor Stoppages-Minor Stoppages-Safety System Fault	3
L16	1/5/2021 11:10	1/5/2021 11:11	MPL-Minor Stoppages-Minor Stoppages-Safety System Fault	4
L16	1/5/2021 11:11	1/5/2021 11:12	MPL-Minor Stoppages-Minor Stoppages-Safety System Fault	4
L16	1/5/2021 11:36	1/5/2021 11:36	MPL-Minor Stoppages-Minor Stoppages-Safety System Fault	5
L16	1/5/2021 11:36	1/5/2021 11:37	MPL-Minor Stoppages-Minor Stoppages-Safety System Fault	5
L16	1/5/2021 19:25	1/5/2021 19:26	MPL-Minor Stoppages-Minor Stoppages-Safety System Fault	7
L10	1/5/2021 11:11	1/5/2021 11:12	MPL-Minor Stoppages-Minor Stoppages-Safety System Fault	1
L10	1/5/2021 11:36	1/5/2021 11:36	MPL-Minor Stoppages-Minor Stoppages-Safety System Fault	2
L10	1/5/2021 11:36	1/5/2021 11:37	MPL-Minor Stoppages-Minor Stoppages-Safety System Fault	2
L10	1/5/2021 19:25	1/5/2021 19:26	MPL-Minor Stoppages-Minor Stoppages-Safety System Fault	3

Kind regards,
and thanks for all the help!

[v-yueyunzh-msft · Accepted solution]

Hi , @Byte_me 

Accoridng to your description, you want to get the [EventID] group by the [Area] and if compare the [Satrt Time ] and the [End Time].

First we need to add an index column in Power Query Editor.

Here are the steps you can refer to :
[1] If you want to realize it in Power Query Editor, you can put this M code in the "Advanced Editor" to refer to :

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tZExC4MwEIX/Ssis6J1oMWulUwTBURwypFaoRjQt+O/rWK6WFkzG73GPj+M1DZeQ8YBDlEYYI7BMIBDGjatChuebGjttnnpmth80b4O9Nr5zLlL4v71d03aycVnJsOxHM7PammlSnV4+uFZXbVdWr4vVA7uox91+MYBfA4CAmAbg3gE0QOeOZDfw7Tg5duQCUxoc/2N3Ysd7UIePPX47ju8R+92jfQE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Area = _t, StartTime = _t, EndTime = _t, #"Event type" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"StartTime", type datetime}, {"EndTime", type datetime}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", (x)=>
List.Sum(Table.AddColumn(Table.FromRows(List.Zip({List.Skip(Table.SelectRows(#"Added Index",(y)=> y[Area]=x[Area] and y[Index]<= x[Index] )[StartTime])  ,List.RemoveLastN(Table.SelectRows(#"Added Index",(y)=> y[Area]=x[Area] and y[Index]<= x[Index])[EndTime],1)  }))  ,"test" , (z)=> if z[Column1]=z[Column2] then 0 else 1     )[test] &{1}
)    )
in
    #"Added Custom"

[2]If you want to realize it in Power BI Desktop , we need to create some calculated column like this:

Index by area = rankx(filter('Table','Table'[Area]=EARLIER('Table'[Area])),'Table'[Index],,ASC)

Flag = 
var _lastendtime=CALCULATE(MAX('Table'[EndTime]),FILTER('Table','Table'[Index by area]=EARLIER('Table'[Index by area])-1&&'Table'[Area]=EARLIER('Table'[Area])))
var _starttime='Table'[StartTime]
return
IF([Index by area]=1,0,IF(_lastendtime=_starttime,0,1))

Event ID = SUMX(FILTER('Table','Table'[Area]=EARLIER('Table'[Area])&&'Table'[Index by area]<=EARLIER('Table'[Index by area])),'Table'[Flag])+1

The result in M code is :

The result in Desktop is :

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[lbendlin]

Power BI has no memory and no concept of mutable variables. What you want to do cannot be achieved in DAX.  It may be possible in Power Query via List.Accumulate but the code will be very fragile.

Likely you will have to do these assignments in the upstream system.