Flag rows with the same ID

[GKJARC]

Hi everyone,

In Power BI I have a table with a sequence of events ranging from 'Start' to 'End'. Each sequence has its own ID. Some events+entrydates are duplicate because they can have varying values in another column.

I'm looking for a way to flag the rows for each ID, after event 'Start' and on or before event 'End'.
So the result would look like this (sorted by Entrydate DESC):

ID	Event	EntrydateTime	Flag
24004	Other	22-03-2024 13:45:00	0
24004	End	22-03-2024 13:30:00	1
24004	End	22-03-2024 13:30:00	1
24004	Daily	22-03-2024 12:00:00	1
24004	Start	22-03-2024 11:00:00	0
24004	Start	22-03-2024 11:00:00	0
24004	Other	21-03-2024 21:00:00	0
24004	Other	21-03-2024 21:00:00	0
24004	Other	20-03-2024  21:00:00	0
24003	End	19-03-2024 11:00:00	1
24003	End	19-03-2024 11:00:00	1
24003	Start	19-03-2024 10:00:00	0
24003	Other	19-03-2024 08:00:00	0
24003	Other	19-03-2024 07:45:00	0
24003	Other	18-03-2024 13:00:00	0
24002	End	18-03-2024 12:55:00	1
24002	Daily	18-03-2024 12:00:00	1
24002	Start	18-03-2024 06:30:00	0
24001	End	16-03-2024 18:00:00	1
24001	Daily	16-03-2024 12:00:00	1
24001	Other	16-03-2024 10:00:00	1
24001	Start	16-03-2024 09:00:00	0

The table is approximately 250.000 rows so performance is a consideration. Any ideas how this could be done using DAX would be most welcome. Thanks!

[Anonymous · Accepted solution]

Hi @GKJARC ,

@lbendlin was right, so I took a different approach.

Below is my table:

The following DAX might work for you:

Flag = 
VAR CurrentID = 'Table'[ID]
VAR CurrentDate = 'Table'[  EntrydateTime]
VAR StartDate = CALCULATE(MIN('Table'[  EntrydateTime]), FILTER('Table', 'Table'[  Event] = "Start" && 'Table'[ID] = CurrentID))
VAR EndDate = CALCULATE(MAX('Table'[  EntrydateTime]), FILTER('Table', 'Table'[  Event] = "End" && 'Table'[ID] = CurrentID))
RETURN IF('Table'[  Event] = "Start", 0, IF(AND(CurrentDate > StartDate, CurrentDate <= EndDate), 1, 0))

The final output is shown in the following figure:

Best Regards,

Xianda Tang

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

[lbendlin]

DAX aggregates by default.  So for example your second and third row will appear to DAX as a single, indistinguishable entry.  You need to bring your own Row Number (Index column)  to prevent that.

Your sample data has "Other" events before "Start"  events.  Please clarify.