Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
BroomJ
Frequent Visitor

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

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:

 

Capture.PNG

 

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.

1 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)
           
    )

1.PNG


Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
Greg_Deckler
Super User
Super User

See my article here:

 

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

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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.

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)
           
    )

1.PNG


Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.