Find n Number of Events Between Two Events

[tonmcg]

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 occurs between CLOCK IN and CLOCK OUT events, inlcusive of a CLOCK OUT event, except if these events occur between CLOCK IN and CLOCK OUT events for special TimeOff periods such as an Unpaid Lunch.

In the example below, I've incorrectly categorized the two events between a CLOCK IN and CLOCK OUT period as TRUE, the Badge to Breakroom event at 10:24 AM and the Badge to Arcade event at 10:34 AM, defined in the isWorkEvent column. I need these two events to be FALSE, as shown in the isWorkEvent2 column.

Index	DateID	Daily Clock In Index	Daily Clock Out Index	Employee	DateTime	Event	TimeOff	isWorkEvent	isWorkEvent2
11	20200110	1	11	John Smith	1/10/2020 15:45	CLOCK OUT		TRUE	TRUE
10	20200110	1	11	John Smith	1/10/2020 15:30	Last Task End		TRUE	TRUE
9	20200110	1	11	John Smith	1/10/2020 10:37	CLOCK IN		FALSE	FALSE
8	20200110	1	11	John Smith	1/10/2020 10:37	CLOCK OUT	Unpaid Lunch	FALSE	FALSE
7	20200110	1	11	John Smith	1/10/2020 10:34	Badge to Arcade		TRUE	FALSE
6	20200110	1	11	John Smith	1/10/2020 10:24	Badge to Breakroom		TRUE	FALSE
5	20200110	1	11	John Smith	1/10/2020 10:13	CLOCK IN	Unpaid Lunch	FALSE	FALSE
4	20200110	1	11	John Smith	1/10/2020 10:13	CLOCK OUT		TRUE	TRUE
3	20200110	1	11	John Smith	1/10/2020 10:04	Badge to Breakroom		TRUE	TRUE
2	20200110	1	11	John Smith	1/10/2020 10:02	First Task End		TRUE	TRUE
1	20200110	1	11	John Smith	1/10/2020 6:00	CLOCK IN		FALSE	FALSE

Here are how I currently calculate isWorkEvent:

isWorkEvent = FactEvent[Index] >= FactEvent[Daily Clock In Index] && FactEvent[Index] <= FactEvent[Daily Clock Out Index] && ISBLANK( FactEvent[TimeOffID] ) && FactEvent[Event] <> "CLOCK IN"

How do I adjust isWorkEvent so that the two events between the 10:13 AM CLOCK IN and 10:37 AM CLOCK OUT events are FALSE?

[v-eachen-msft · Accepted solution]

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]

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/3395...