How to find out if X event happened when Y event was occuring

[anisevance]

Hi all,

I have a problem for which I've been struggling to find a good solution in Power BI.

My department manages crisis response teams. Our teams receive 911 calls for service and then go out to those calls. Sometimes, our teams are already on a 911 call for service when another one comes in. We then "miss" this second call. 

Each row in our main database table includes fields for:

Unique Call ID number (auto-generated for each call)

Type of call (which lets us know if our teams are eligible for that call)

Day-Time of call

Unit Dispatched

Day-Time unit was dispatched

Day-Time call ended (i.e. when the unit leaves the scene and is available for another call)

It's easy enough to find out how many calls we missed because we can narrow down the Type of Call field to the calls for which we were eligible, and then subtract the eligible calls to which we were dispatched from the total eligible call volume.

What's not easy: ascertaining if the reason we missed a call was because we were on another call. We might miss calls because a unit is out of service for the day, is out of service for a critical meeting or some other important event, or our staff didn't hear the call come in (this last one is very rare, but it is theoretically conceivable). 

I'd like to know how, using the fields outlined above, I could find how many instances when we missed a call were due to us being on another call. Is this just a long IF/THEN statement, or is there a more elegant/efficient way to do this?

Thanks in advance!

[tamerj1]

Hi @anisevance 

Time related calculations  might be a bit heavy. I would recommend to first create a Day Unit Dispatched column (simply DATEVALUE ( 'Table'[Day-Time Unit Dispatched] ) 

the the flag column can be

Missed =
IF (
'Table'[Heart Eligible] = "Yes"
&& CONTAINSSTRING ( 'Table'[Unit Dispatched], "Police" ),
IF (
NOT ISEMPTY (
FILTER (
CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Day Unit Dispatched] ) ),
'Table'[Day-Time Unit Dispatched] <= EARLIER ( 'Table'[Day-Time Unit Ended] )
&& 'Table'[Day-Time Unit Ended] >= EARLIER ( 'Table'[Day-Time Unit Dispatched] )
&& 'Table'[Heart Eligible] = "Yes"
&& CONTAINSSTRING ( 'Table'[Unit Dispatched], "Heart" )
)
),
"Missed"
)
)

Despite this method is expected to be faster than directly filtering the complete table yet it may have a downside that you may lose track of some calles that occurred during midnight. Therefore you need to double check concerning on these records. 
sorry I didn't prepare a sample file to validate the results however, real data that need to be checked anyway. 

[anisevance]

Thanks @tamerj1, this solution looks quite promising! I will try it and report back how it goes.

[anisevance]

Thanks @FreemanZ! Below, please see a six line hypothetical sample of the data. We pull from a database, but all of the fields below are in the same table in that database. 

Our units are called HEART units. When we don't respond to a 911 call, police responds. So, you'll see HEART1, Police1, and Police2 in the Unit Dispatched field. 

What we would like to output: HEART missed one call for which we are eligible (call #2) because we were on another call (call #1) when call #2 came in (i.e. when a unit was dispatched). 

Unique ID	Type	Heart Eligible	Unit Dispatched	Day-Time Unit Dispatched	Day-Time Call Ended
1	TRESPASS	Yes	Heart1	1-1-2023 0900	1-1-2023 0930
2	PANHANDLING	Yes	Police1	1-1-2023 0915	1-1-2023 0940
3	LARCENY	No	Police2	1-1-2023 0920	1-1-2023 1015
4	MENTAL HEALTH CRISIS	Yes	Heart1	1-1-2023 1000	1-1-2023 1115
5	TRAFFIC ACCIDENT	No	Police1	1-1-2023 1030	1-1-2023 1200
6	SUICIDE THREAT	Yes	Heart1	1-1-2023 1130	1-1-2023 1300

[FreemanZ]

hi @anisevance 

the fields you mentioned in the database, are they from the same table? if not, how are they related?

It would be easier if you could provide some sample dataset with expected result.

[anisevance]

Thanks @FreemanZ! Below, please see a six line hypothetical sample of the data. We pull from a database, but all of the fields below are in the same table in that database. 

Our units are called HEART units. When we don't respond to a 911 call, police responds. So, you'll see HEART1, Police1, and Police2 in the Unit Dispatched field. 

What we would like to output: HEART missed one call for which we are eligible (call #2) because we were on another call (call #1) when call #2 came in (i.e. when a unit was dispatched). 

Unique ID	Type	Heart Eligible	Unit Dispatched	Day-Time Unit Dispatched	Day-Time Call Ended
1	TRESPASS	Yes	Heart1	1-1-2023 0900	1-1-2023 0930
2	PANHANDLING	Yes	Police1	1-1-2023 0915	1-1-2023 0940
3	LARCENY	No	Police2	1-1-2023 0920	1-1-2023 1015
4	MENTAL HEALTH CRISIS	Yes	Heart1	1-1-2023 1000	1-1-2023 1115
5	TRAFFIC ACCIDENT	No	Police1	1-1-2023 1030	1-1-2023 1200
6	SUICIDE THREAT	Yes	Heart1	1-1-2023 1130	1-1-2023 1300