Forum Discussion
Flag rows with the same ID
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!
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.
2 Replies
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.
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.
