Find n Number of Events Between Two Events

Question

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 Answer

Hi&nbsp;tonmcg&nbsp;,
&nbsp;
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] &gt;= FactEvent[Daily Clock In Index] )
    &amp;&amp; ( FactEvent[Index] &lt;= FactEvent[Daily Clock Out Index] )
    &amp;&amp; ( FactEvent[TimeOff] = BLANK () )
    &amp;&amp; ( FactEvent[Event] &lt;&gt; "CLOCK IN" )
    &amp;&amp; OR ( FactEvent[Index] &gt; a, FactEvent[Index] &lt; b )
Here is the result.

&nbsp;

Forum Discussion

Find n Number of Events Between Two Events

2 Replies

Related content

MQTT Events debugging

Storage trigger event

How to drop duplicates in event stream using event processor ?

Events Dashboard

Realtime - Business Events - cannot create business event shcema