Identify row with same ID (condition: 24 hours)

[aymeric_kolan]

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” :

• 1: It’s because I have one row 24 hours after with same ID
• 0: there is no row with same ID

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!

[v-jiascu-msft]

Hi @aymeric_kolan,

I have a few questions.

1. “a row with the same InboundVoiceCalls_CallId (ID)”. I can’t see any rows with the same InboundVoiceCalls_Callid(ID) in the sample.
2. “more than once in 1 day” and “in the next 24 hours”. Do you mean 00:00:00 to 23:59:59 or the next 24 hours from a special time.

If these questions were clarified, the formula could be easy.

Best Regards!

Dale

[aymeric_kolan]

Hi  @v-jiascu-msft

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 ! 🙂

[v-jiascu-msft · Accepted solution]

@aymeric_kolan

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

[aymeric_kolan]

Thank you @v-jiascu-msft.

It works 🙂

[v-jiascu-msft]

My pleasure! I am so glad it helps.

Best Regards!

Dale