Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hi all,
I’m working on IVR data for customer service and I’m looking for identify phone number who try to contact us more than once in 1 day…
For each rows, I need to know if there is a row with the same InboundVoiceCalls_CallId (ID) in the next 24 hours.
Columns :
- calltime (date and hours)
- InboundVoiceCalls_CallId (ID)
- CallCLID (phone number)
For example, you can see in colums “reit 1d” :
CallTime | InboundVoiceCalls_CallId | CallCLID | Réit1d |
02/01/2017 10:03 | EI227-C64756 | 5776638 | 1 |
02/01/2017 10:14 | EI227-C64901 | 5776638 | 1 |
02/01/2017 17:05 | EI228-C2453 | 5776638 | 1 |
02/01/2017 17:17 | EI228-C2516 | 5776638 | 0 |
02/02/2017 09:15 | EI228-C18825 | 5776638 | 0 |
09/02/2017 11:42 | EI228-C21617 | 5776638 | 1 |
09/02/2017 11:47 | EI228-C21625 | 5776638 | 1 |
09/02/2017 15:22 | EI228-C21850 | 5776638 | 0 |
07/03/2017 16:52 | EI230-C4967 | 5776638 | 0 |
31/03/2017 17:02 | EI230-C10444 | 5776638 | 0 |
03/04/2017 13:55 | EI230-C10642 | 5776638 | 1 |
04/04/2017 10:37 | EI230-C10903 | 5776638 | 0 (there is more than 24 hours w/ next row) |
05/04/2017 11:33 | EI230-C11292 | 5776638 | 0 |
05/04/2017 15:16 | EI230-C11389 | 5776638 | 0 |
I tried to do a calculated column:
Réit1j = IF(
COUNTROWS(
FILTER(
'DATA SVI_2';
'DATA SVI_2'[CallCLID] = EARLIER('DATA SVI_2'[CallCLID]) &&
'DATA SVI_2'[CallTime].[Année] = EARLIER('DATA SVI_2'[CallTime].[Année]) &&
'DATA SVI_2'[CallTime].[Mois] = EARLIER('DATA SVI_2'[CallTime].[Mois]) &&
'DATA SVI_2'[CallTime].[Jour] = EARLIER('DATA SVI_2'[CallTime].[Jour]) - 1
)
) > 0;
TRUE;
FALSE
)
Do you have some ideas how I can do that?
Thank you for your help!
Solved! Go to Solution.
Hi,
Please try this formula as a calculated column. If you want to change the time duration, just change the DeadLine part.
Réit1j = VAR DeadLine = 'DATA SVI_2'[CallTime] + 1 RETURN IF ( CALCULATE ( COUNTROWS ( 'DATA SVI_2' ), FILTER ( 'DATA SVI_2', 'DATA SVI_2'[CallTime] > EARLIER ( 'DATA SVI_2'[CallTime] ) && 'DATA SVI_2'[CallTime] <= DeadLine && 'DATA SVI_2'[CallCLID] = EARLIER ( 'DATA SVI_2'[CallCLID] ) ) ) >= 1, 1, 0 )
Best Regards!
Dale
Hi @aymeric_kolan,
I have a few questions.
If these questions were clarified, the formula could be easy.
Best Regards!
Dale
1. You're right. In fact, InboundVoiceCalls_CallId is a system ID but we have to check on CallCLID (CallCLID is the phone number).
2. Good question, I think we need to look the day after.
For example, if a have a row at 10:00:00 on Monday, we want to check until 23:59:59 on Thusday and have 1 if there is another row with same CallCLID at 18:50:00 on Thusday.
Thank you ! 🙂
Hi,
Please try this formula as a calculated column. If you want to change the time duration, just change the DeadLine part.
Réit1j = VAR DeadLine = 'DATA SVI_2'[CallTime] + 1 RETURN IF ( CALCULATE ( COUNTROWS ( 'DATA SVI_2' ), FILTER ( 'DATA SVI_2', 'DATA SVI_2'[CallTime] > EARLIER ( 'DATA SVI_2'[CallTime] ) && 'DATA SVI_2'[CallTime] <= DeadLine && 'DATA SVI_2'[CallCLID] = EARLIER ( 'DATA SVI_2'[CallCLID] ) ) ) >= 1, 1, 0 )
Best Regards!
Dale
My pleasure! I am so glad it helps.
Best Regards!
Dale
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
75 | |
63 | |
51 | |
48 |
User | Count |
---|---|
211 | |
86 | |
64 | |
59 | |
56 |