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.

     

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 years ago

Seems like you would just check the TimeOff value of the MAX CLOCK OUT event < current date/time and make sure the date/time is less than the next CLOCK IN even. If the TimeOff of the MAX CLOCK OUT event < current data/time of row is Unpaid lunch and there is no CLOCK IN event between that time and the current date/time. FALSE. 

 

See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586