Measure Help - Identify 2 different events within xx minutes of eachother.

[tagban]

I'm looking into showing a value so I have the following data
UNIQUEID1 | OutboundCall | 1/30/2024 5:00:00 PM EDT

UNIQUEID2 | OutboundSMS | 1/30/2024 4:30:00 PM EDT

UNIQUEID3 | DENIED | 1/30/2024 4:45:00 PM EDT
UNIQUEID4 | OutboundCall | 1/30/2024 5:10:00 PM EDT

What I want is I want an alert/some sort of way to flag the "Denied" when outside of 10 minutes before the Call happens. IE i want to identify ANYTIME a staff member denies an incoming call with more than 10 minutes before an Outbound Call. 

I suspect I need to create an index column to capture this data but am at a loss. I've read a few similar things but they seem to be in reverse. Maybe I just need to capture the data related to the resulting alert when a call is denied > 10 mins before an OutboundCall event?

[tagban]

Thank you everyone for the help I apologize when I posted this I wasn't a bit clearer. Basically we have a rule that if someone declines a call it must be for the reason they're about to make an outbound call. They want me to somehow flag the rejections when declined and the SAME staff member moves onto make an outbound call > 10 mins after. I got some better dummy data put together, granted this isn't all of it but I do know that I'm guessing I'll need to index the entire thing after sorting by datetime column, then probably write some M query to actually discern this inside the Power Query rather than as a measure or calculated column.

Here's the data I provided as a reply to someone that better reflects what I'm looking at in the DB:

StaffName	EventId	EventName	StartTime	Within10Mins
STAFFA	7BA2C827-E758-4EAE	OutboundSMS	1/29/24 10:12 AM
STAFFB	38811614-DAD1-4597	OutboundCall	1/29/24 10:15 AM
STAFFA	11B31878-62F1-45CE	OutboundCall	1/29/24 10:21 AM
STAFFC	0B9B8F35-E473-40EA	OutboundCall	1/29/24 10:24 AM
STAFFB	22537504-9DA1-48E0	OutboundCall	1/29/24 10:26 AM
STAFFA	0E036553-CA16-4280	OutboundSMS	1/29/24 10:26 AM
STAFFC	5BC24A7C-F212-459F	OutboundSMS	1/29/24 10:31 AM
STAFFC	722FF7CD-CB23-4E5B	OutboundSMS	1/29/24 10:34 AM
STAFFC	159B5D71-9BCA-4803	OutboundSMS	1/29/24 10:51 AM
STAFFC	676CAB3D-8827-4A89	OutboundCall	1/29/24 10:59 AM
STAFFC	8C5806AF-DBFE-4F7E	OutboundCall	1/29/24 10:59 AM
STAFFA	CA71279A558C06C20D	InboundCall	1/29/24 11:06 AM
STAFFC	9741198F-B1E1-455A	OfferRejected	1/29/24 11:51 AM	Y
STAFFC	70B9515B-E197-45F2	OutboundCall	1/29/24 12:00 PM
STAFFB	554A7FF1-C5A7-40A6	OutboundCall	1/29/24 12:02 PM
STAFFB	0B0348B8-6A6B-4F19	OutboundCall	1/29/24 12:05 PM
STAFFB	5660CB90-657E-426F	OutboundCall	1/29/24 12:07 PM
STAFFB	58847511-010B-4E76	OutboundCall	1/29/24 12:09 PM
STAFFA	65F6C607-C63E-4A0E	OutboundCall	1/29/24 12:10 PM
STAFFB	265AB006-5BC6-45E5	OutboundCall	1/29/24 12:11 PM
STAFFB	51ADBD13-6B48-4E20	OutboundCall	1/29/24 12:13 PM
STAFFB	0390BABA-37CB-4535	OutboundCall	1/29/24 12:16 PM
STAFFC	5D2529DC-986F-4013	OutboundCall	1/29/24 12:19 PM
STAFFB	D44D0580-711A-40E5	OutboundCall	1/29/24 12:22 PM
STAFFB	9979B069-8EC2-43D3	OutboundCall	1/29/24 12:24 PM
STAFFB	A1E31BFE-B2E6-42DE	OutboundCall	1/29/24 12:30 PM
STAFFC	6FA58EF7-2022-4DCB	OutboundCall	1/29/24 12:30 PM
STAFFA	AG-7A3D4FC6-3314-4	OfferRejected	1/29/24 12:11 PM	N
STAFFB	22B7FDB9-5B8B-4506	OutboundCall	1/29/24 12:33 PM
STAFFA	548AEB4F-FF58-494D	OutboundCall	1/29/24 12:36 PM
STAFFB	03A27CAF-F2E1-4BEB	OutboundCall	1/29/24 12:38 PM
STAFFB	480C477E-8BE3-46B1	OutboundCall	1/29/24 12:41 PM

[Anonymous]

Hi @tagban 

Please try this:

With your sample data, I create a calculate column:

Alert = 
VAR _perviousTime = CALCULATE(
                        MAX('Table'[time]),
                        FILTER(
                            'Table',
                            'Table'[time]<EARLIER('Table'[time])
                            )
                        )
//get the time earlier than the current time
VAR _TimeInterval = DATEDIFF(_perviousTime,
                        'Table'[time],
                        MINUTE
                        )
RETURN IF('Table'[type]="DENIED"&&_TimeInterval>10,
        1
        )

The result is as follow: 

Best Regards,

Zhengdong Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

Share more cases of Denied and show the expected result in a spare column.

[ryan_mayu · Accepted solution]

@tagban 

maybe you can try this

1. create an index column

2. use DAX to createa a column

Column =

if('Table'[Type]="DENIED"&&maxx(FILTER('Table','Table'[Index]=EARLIER('Table'[Index])+1),'Table'[Type])="OutboundCall"&&DATEDIFF('Table'[time],maxx(FILTER('Table','Table'[Index]=EARLIER('Table'[Index])+1),'Table'[time]),MINUTE)>10,"Y")

pls see the attachment below

[tagban]

This actually works really good except I have a small issue I forgot to mention its got a username column also, so its per username. I'll dummy up some better data to look at because I didn't obviously make that clear, and I was very very tired (working like 16 hr days when I made the original post).

The last column below is what I'm looking for, You'll notice the OutboundCall is associated with STAFF that rejected the last call and what we're trying to do is simply flag the ones that are rejected against a rule fo 10 mins or more from the last outbound call. So in this case the Y would indicate they followed the rule, N would be a violation.

StaffName	EventId	EventName	StartTime	Within10Mins
STAFFA	7BA2C827-E758-4EAE	OutboundSMS	1/29/24 10:12 AM
STAFFB	38811614-DAD1-4597	OutboundCall	1/29/24 10:15 AM
STAFFA	11B31878-62F1-45CE	OutboundCall	1/29/24 10:21 AM
STAFFC	0B9B8F35-E473-40EA	OutboundCall	1/29/24 10:24 AM
STAFFB	22537504-9DA1-48E0	OutboundCall	1/29/24 10:26 AM
STAFFA	0E036553-CA16-4280	OutboundSMS	1/29/24 10:26 AM
STAFFC	5BC24A7C-F212-459F	OutboundSMS	1/29/24 10:31 AM
STAFFC	722FF7CD-CB23-4E5B	OutboundSMS	1/29/24 10:34 AM
STAFFC	159B5D71-9BCA-4803	OutboundSMS	1/29/24 10:51 AM
STAFFC	676CAB3D-8827-4A89	OutboundCall	1/29/24 10:59 AM
STAFFC	8C5806AF-DBFE-4F7E	OutboundCall	1/29/24 10:59 AM
STAFFA	CA71279A558C06C20D	InboundCall	1/29/24 11:06 AM
STAFFC	9741198F-B1E1-455A	OfferRejected	1/29/24 11:51 AM	Y
STAFFC	70B9515B-E197-45F2	OutboundCall	1/29/24 12:00 PM
STAFFB	554A7FF1-C5A7-40A6	OutboundCall	1/29/24 12:02 PM
STAFFB	0B0348B8-6A6B-4F19	OutboundCall	1/29/24 12:05 PM
STAFFB	5660CB90-657E-426F	OutboundCall	1/29/24 12:07 PM
STAFFB	58847511-010B-4E76	OutboundCall	1/29/24 12:09 PM
STAFFA	65F6C607-C63E-4A0E	OutboundCall	1/29/24 12:10 PM
STAFFB	265AB006-5BC6-45E5	OutboundCall	1/29/24 12:11 PM
STAFFB	51ADBD13-6B48-4E20	OutboundCall	1/29/24 12:13 PM
STAFFB	0390BABA-37CB-4535	OutboundCall	1/29/24 12:16 PM
STAFFC	5D2529DC-986F-4013	OutboundCall	1/29/24 12:19 PM
STAFFB	D44D0580-711A-40E5	OutboundCall	1/29/24 12:22 PM
STAFFB	9979B069-8EC2-43D3	OutboundCall	1/29/24 12:24 PM
STAFFB	A1E31BFE-B2E6-42DE	OutboundCall	1/29/24 12:30 PM
STAFFC	6FA58EF7-2022-4DCB	OutboundCall	1/29/24 12:30 PM
STAFFA	AG-7A3D4FC6-3314-4	OfferRejected	1/29/24 12:11 PM	N
STAFFB	22B7FDB9-5B8B-4506	OutboundCall	1/29/24 12:33 PM
STAFFA	548AEB4F-FF58-494D	OutboundCall	1/29/24 12:36 PM
STAFFB	03A27CAF-F2E1-4BEB	OutboundCall	1/29/24 12:38 PM
STAFFB	480C477E-8BE3-46B1	OutboundCall	1/29/24 12:41 PM

[Daniel29195]

Hello @tagban 

sorry got a little bit confused about the logic that you want . 

could  you please rephrase the logic of the dax that you want ? 

best regards,