Forum Discussion

tonmcg's avatar
tonmcg
Icon for Resolver II rankResolver II
6 years ago
Solved

Find n Number of Events Between Two Events

I have the following table, FactEvent. It details all the events an employee logs every day. I need to categorize each event as a work event, either TRUE or FALSE. A work event is any event that occu...
Need Help
  • v-eachen-msft's avatar
    v-eachen-msft
    6 years ago

    Hi tonmcg ,

     

    You could refer to the following DAX:

    isWorkEvent =
VAR a =
    CALCULATE (
        MAX ( FactEvent[Index] ),
        FILTER ( FactEvent, FactEvent[TimeOff] = "Unpaid Lunch" )
    )
VAR b =
    CALCULATE (
        MIN ( FactEvent[Index] ),
        FILTER ( FactEvent, FactEvent[TimeOff] = "Unpaid Lunch" )
    )
RETURN
    ( FactEvent[Index] >= FactEvent[Daily Clock In Index] )
    && ( FactEvent[Index] <= FactEvent[Daily Clock Out Index] )
    && ( FactEvent[TimeOff] = BLANK () )
    && ( FactEvent[Event] <> "CLOCK IN" )
    && OR ( FactEvent[Index] > a, FactEvent[Index] < b )

    Here is the result.

     

v-eachen-msft's avatar
v-eachen-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi tonmcg ,

 

You could refer to the following DAX:

isWorkEvent =
VAR a =
    CALCULATE (
        MAX ( FactEvent[Index] ),
        FILTER ( FactEvent, FactEvent[TimeOff] = "Unpaid Lunch" )
    )
VAR b =
    CALCULATE (
        MIN ( FactEvent[Index] ),
        FILTER ( FactEvent, FactEvent[TimeOff] = "Unpaid Lunch" )
    )
RETURN
    ( FactEvent[Index] >= FactEvent[Daily Clock In Index] )
    && ( FactEvent[Index] <= FactEvent[Daily Clock Out Index] )
    && ( FactEvent[TimeOff] = BLANK () )
    && ( FactEvent[Event] <> "CLOCK IN" )
    && OR ( FactEvent[Index] > a, FactEvent[Index] < b )

Here is the result.