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

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
Icon for Microsoft Employee rankMicrosoft 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
      Icon for Microsoft Employee rankMicrosoft Employee
      9 years ago

      My pleasure! I am so glad it helps.

       

      Best Regards!

      Dale