Count of events with time constraints

[805]

Hi Community, 

I need some help with building a calculated column.  

Here's the scenario:

if 4 records/rows (events) happened to the same entity within 4 hours I need to flag those events.   

example data: 

Entity | Event | Desired Flag

123 | 3/17/2020 2:04:00 AM | No 

123 | 3/17/2020 3:20:00 AM | No

456 | 3/17/2020 9:00:00 AM | Yes

456 | 3/17/2020 9:40:00 AM | Yes

456 | 3/17/2020 10:30:00 AM | Yes

456 | 3/17/2020 11:10:00 AM | Yes

456 | 3/20/2020 12:00:00 PM | No

Thanks. 

[AlB]

@805 

Try this

Flag V2 = 
VAR eventsIn4hWindow_ =
    CALCULATETABLE (
        DISTINCT ( Table1[Event] ),
        ALLEXCEPT ( Table1, Table1[Entity] ),
        Table1[Event] >= (EARLIER ( Table1[Event] ) - ( 4 / 24 )),
        Table1[Event]<= (EARLIER ( Table1[Event] ) + ( 4 / 24 ))
    )
VAR checkT_ =
    FILTER (
        eventsIn4hWindow_,
        COUNTROWS (
            FILTER (
                eventsIn4hWindow_,
                Table1[Event] >= EARLIER ( Table1[Event] ) && Table1[Event]<= (EARLIER ( Table1[Event] ) + ( 4 / 24 ))
            )
        ) >=4
    )
RETURN
    IF ( ISEMPTY ( checkT_ ), "No", "Yes" )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB]

Hi @805 

You need to specify  bit more.

Are we looking at four events within the same day? CAn the number of events in one day be > 4?

What happens if there are more than 4 events than fall in the same four hour period?

What in a case like this? What would be the right flags?

456 | 3/17/2020 9:00:00 AM | Yes

456 | 3/17/2020 9:40:00 AM | Yes

456 | 3/17/2020 10:30:00 AM | Yes

456 | 3/17/2020 11:10:00 AM | Yes

456 | 3/17/2020 12:30:00 AM | Yes

456 | 3/17/2020 13:10:00 AM | Yes

456 | 3/17/2020 15:10:00 AM | Yes

456 | 3/17/2020 16:10:00 AM | Yes

456 | 3/17/2020 17:05:00 AM | Yes

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[805]

Hey @AIB  Looking to flag events that occur in any 4 hour period so it's possible they could cross into the following day (11PM-3 AM.) The number of events can be greater than 4 in a 4 hour period.  

- What happens if there are more than 4 events than fall in the same four hour period? - All events for that entity need to be flagged.  For context this is related to MTP (massive transfusion protocol) related to blood transfusions.