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.

Anonymous's avatar
Anonymous
Not applicable
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.