Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
tagban
Helper I
Helper I

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

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?

1 ACCEPTED SOLUTION
ryan_mayu
Super User
Super User

@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")
 
11.PNG
pls see the attachment below
 




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

6 REPLIES 6
tagban
Helper I
Helper I

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:

StaffNameEventIdEventNameStartTimeWithin10Mins
STAFFA7BA2C827-E758-4EAEOutboundSMS1/29/24 10:12 AM 
STAFFB38811614-DAD1-4597OutboundCall1/29/24 10:15 AM 
STAFFA11B31878-62F1-45CEOutboundCall1/29/24 10:21 AM 
STAFFC0B9B8F35-E473-40EAOutboundCall1/29/24 10:24 AM 
STAFFB22537504-9DA1-48E0OutboundCall1/29/24 10:26 AM 
STAFFA0E036553-CA16-4280OutboundSMS1/29/24 10:26 AM 
STAFFC5BC24A7C-F212-459FOutboundSMS1/29/24 10:31 AM 
STAFFC722FF7CD-CB23-4E5BOutboundSMS1/29/24 10:34 AM 
STAFFC159B5D71-9BCA-4803OutboundSMS1/29/24 10:51 AM 
STAFFC676CAB3D-8827-4A89OutboundCall1/29/24 10:59 AM 
STAFFC8C5806AF-DBFE-4F7EOutboundCall1/29/24 10:59 AM 
STAFFACA71279A558C06C20DInboundCall1/29/24 11:06 AM 
STAFFC9741198F-B1E1-455AOfferRejected1/29/24 11:51 AMY
STAFFC70B9515B-E197-45F2OutboundCall1/29/24 12:00 PM 
STAFFB554A7FF1-C5A7-40A6OutboundCall1/29/24 12:02 PM 
STAFFB0B0348B8-6A6B-4F19OutboundCall1/29/24 12:05 PM 
STAFFB5660CB90-657E-426FOutboundCall1/29/24 12:07 PM 
STAFFB58847511-010B-4E76OutboundCall1/29/24 12:09 PM 
STAFFA65F6C607-C63E-4A0EOutboundCall1/29/24 12:10 PM 
STAFFB265AB006-5BC6-45E5OutboundCall1/29/24 12:11 PM 
STAFFB51ADBD13-6B48-4E20OutboundCall1/29/24 12:13 PM 
STAFFB0390BABA-37CB-4535OutboundCall1/29/24 12:16 PM 
STAFFC5D2529DC-986F-4013OutboundCall1/29/24 12:19 PM 
STAFFBD44D0580-711A-40E5OutboundCall1/29/24 12:22 PM 
STAFFB9979B069-8EC2-43D3OutboundCall1/29/24 12:24 PM 
STAFFBA1E31BFE-B2E6-42DEOutboundCall1/29/24 12:30 PM 
STAFFC6FA58EF7-2022-4DCBOutboundCall1/29/24 12:30 PM 
STAFFAAG-7A3D4FC6-3314-4OfferRejected1/29/24 12:11 PMN
STAFFB22B7FDB9-5B8B-4506OutboundCall1/29/24 12:33 PM 
STAFFA548AEB4F-FF58-494DOutboundCall1/29/24 12:36 PM 
STAFFB03A27CAF-F2E1-4BEBOutboundCall1/29/24 12:38 PM 
STAFFB480C477E-8BE3-46B1OutboundCall1/29/24 12:41 PM 
v-zhengdxu-msft
Community Support
Community Support

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: 

vzhengdxumsft_0-1706670643249.png

 

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
Super User
Super User

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
ryan_mayu
Super User
Super User

@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")
 
11.PNG
pls see the attachment below
 




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.

StaffNameEventIdEventNameStartTimeWithin10Mins
STAFFA7BA2C827-E758-4EAEOutboundSMS1/29/24 10:12 AM 
STAFFB38811614-DAD1-4597OutboundCall1/29/24 10:15 AM 
STAFFA11B31878-62F1-45CEOutboundCall1/29/24 10:21 AM 
STAFFC0B9B8F35-E473-40EAOutboundCall1/29/24 10:24 AM 
STAFFB22537504-9DA1-48E0OutboundCall1/29/24 10:26 AM 
STAFFA0E036553-CA16-4280OutboundSMS1/29/24 10:26 AM 
STAFFC5BC24A7C-F212-459FOutboundSMS1/29/24 10:31 AM 
STAFFC722FF7CD-CB23-4E5BOutboundSMS1/29/24 10:34 AM 
STAFFC159B5D71-9BCA-4803OutboundSMS1/29/24 10:51 AM 
STAFFC676CAB3D-8827-4A89OutboundCall1/29/24 10:59 AM 
STAFFC8C5806AF-DBFE-4F7EOutboundCall1/29/24 10:59 AM 
STAFFACA71279A558C06C20DInboundCall1/29/24 11:06 AM 
STAFFC9741198F-B1E1-455AOfferRejected1/29/24 11:51 AMY
STAFFC70B9515B-E197-45F2OutboundCall1/29/24 12:00 PM 
STAFFB554A7FF1-C5A7-40A6OutboundCall1/29/24 12:02 PM 
STAFFB0B0348B8-6A6B-4F19OutboundCall1/29/24 12:05 PM 
STAFFB5660CB90-657E-426FOutboundCall1/29/24 12:07 PM 
STAFFB58847511-010B-4E76OutboundCall1/29/24 12:09 PM 
STAFFA65F6C607-C63E-4A0EOutboundCall1/29/24 12:10 PM 
STAFFB265AB006-5BC6-45E5OutboundCall1/29/24 12:11 PM 
STAFFB51ADBD13-6B48-4E20OutboundCall1/29/24 12:13 PM 
STAFFB0390BABA-37CB-4535OutboundCall1/29/24 12:16 PM 
STAFFC5D2529DC-986F-4013OutboundCall1/29/24 12:19 PM 
STAFFBD44D0580-711A-40E5OutboundCall1/29/24 12:22 PM 
STAFFB9979B069-8EC2-43D3OutboundCall1/29/24 12:24 PM 
STAFFBA1E31BFE-B2E6-42DEOutboundCall1/29/24 12:30 PM 
STAFFC6FA58EF7-2022-4DCBOutboundCall1/29/24 12:30 PM 
STAFFAAG-7A3D4FC6-3314-4OfferRejected1/29/24 12:11 PMN
STAFFB22B7FDB9-5B8B-4506OutboundCall1/29/24 12:33 PM 
STAFFA548AEB4F-FF58-494DOutboundCall1/29/24 12:36 PM 
STAFFB03A27CAF-F2E1-4BEBOutboundCall1/29/24 12:38 PM 
STAFFB480C477E-8BE3-46B1OutboundCall1/29/24 12:41 PM 
Daniel29195
Super User
Super User

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,

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.