Getting the time difference between two separate rows with a matching correlation ID

[BroomJ]

Hi All,

I'm currently using Power BI to connect to the Skype for Business CDR database in order to produce reports on the volume, duration and other such metrics of calls.

One of the metrics I would like to track is the length of time that callers are waiting for someone to pick up after getting transferred from the main desk through to the service desk. If this is actually stored somewhere in a column in the CDR database, I can't find it (feel free to tell me if there is!), so I am instead trying to calculate the difference between the time the main desk picks up and transfers the call and the time someone on the service desk picks up the call.

I have the required data, but I am not sure what DAX I need to write in order to get the calculation I need. Here is a snippet of the data:

As you can see in the top example, the "main" automated service desk successfully picks up the phone at 15:02:09, transfers to all the service desk users, of which Cheryl then picks up at 15:02:50, leaving the caller waiting for 41 seconds for an answer. These two calls have a response code of 200, which is a successful call, so I know that the automated service desk successfully picked up and transferred the call to the group, which was then answered by Cheryl. You can also see that they share the same correlation ID, so they are part of the same chain (session) within a response group.

Is there a way I can use DAX to calculate that difference (41 seconds in the top example, 52 seconds in the bottom example)  for every call in the above format?

Any help would be greatly appreciated.

[Greg_Deckler]

See my article here:

https://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486

[BroomJ]

Apologies if I'm misunderstanding your blog, but it appears that the DAX is being used to take the average of all columns and format it into a time-like format. Is this correct?

My problem is that I want to get the DATEDIFF (in seconds) between the "main" row with a response code of 200 and the other row that has a response code of 200 (in the case of the examples, Cheryl) within the same Correlation ID.

[v-yuezhe-msft · Accepted solution]

Hi @BroomJ,

Create a column in your table using the formula below and see my example in the following screenshot.

Duration = 
VAR Lasttime =
    CALCULATE (
       FIRSTNONBLANK( Table1[InviteTime],""),
        FILTER(Table1,
            Table1[CorrelationID]
                =  EARLIER ( Table1[CorrelationID])
                && Table1[ResponseCode] = EARLIER ( Table1[ResponseCode])
                )
        )
    
RETURN
    IF (
        ISBLANK (Lasttime),
        0,
      DATEDIFF( Lasttime,Table1[InviteTime],SECOND)
           
    )

Thanks,
Lydia Zhang

[BroomJ]

Hi Lydia,

I've just plugged the formula you posted into a column and it's doing exactly what I want.

Thank you very much for your help!