Forum Discussion

aymeric_kolan's avatar
aymeric_kolan
Frequent Visitor
9 years ago
Solved

Identify row with same ID (condition: 24 hours)

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...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    9 years ago

    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

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
9 years ago

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's avatar
aymeric_kolan
Frequent Visitor
9 years ago

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's avatar
    v-jiascu-msft
    Microsoft Employee
    9 years ago

    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

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Microsoft Employee
        9 years ago

        My pleasure! I am so glad it helps.

         

        Best Regards!

        Dale