Forum Discussion

BroomJ's avatar
BroomJ
Frequent Visitor
9 years ago
Solved

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

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

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.

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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

    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!