Forum Discussion

GKJARC's avatar
GKJARC
Resolver I
2 years ago
Solved

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 v...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
lbendlin
Super User
2 years ago

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.