Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Calculate Average Response Time based on other columns

I am trying to work out the average response time to automated SMS's. The problem is my records are a one to many relationship on the alertID, so one AlertID can have many records, as the records show the sequence of events for that alertID.

So the Sent time is the row where the FIRST/earliest result code = TNS, there can be multiple TNS result codes for the same alert, I need the first one. Then from that first time TNS record the device start time is the sent time.

Then the response time is the record where result code = TCS or TFS

So the average response time is response time – sent time

I am really struggling to find the correct power bi logic to do this.

 

Below is a sample of my data:

AlertIDDeviceDevice Start TimeResult CodeClient Code
1SMS2019/08/04 11:06TNS 
1SMS2019/08/04 11:12TCS 
1  SM1 
1  SMDMessage successfully delivered.
1  SMDMessage successfully delivered.
2SMS2019/08/04 10:00TNS 
2SMS2019/08/04 10:15TNS 
2 2019/08/04 10:30TFS 
2  SMDMessage successfully delivered.
2  SMDMessage successfully delivered.
3SMS2019/08/04 12:30TNS 
3SMS2019/08/04 12:45TNS 
3 2019/08/04 13:00TCS 
3  SMDMessage successfully delivered.
3  SMDMessage successfully delivered.
4SMS2019/08/05 11:00TNS 
4SMS2019/08/04 11:15TFS 
4  SMDMessage successfully delivered.
4  SMDMessage successfully delivered.

I am literally just trying to calculate on average how long does it take to get a response once the first SMS is sent, to when the response is received.

 

Please, please. Your help is much appreciated :) 

Thank you in advance.

Need Help
Tips and Tricks

4 Replies

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Hi Anonymous 

    I assume it's average across the IDs that you are looking for:

    Measure =
VAR AuxTable_ =
    ADDCOLUMNS (
        DISTINCT ( Table1[AlertID] ),
        "ResponseTime",
        VAR Sent_ =
            CALCULATE (
                MIN ( Table1[Device Start Time] ),
                Table1[Result Code] = "TNS",
                ALLEXCEPT ( Table1, Table1[AlertID] )
            )
        VAR End_ =
            CALCULATE (
                MIN ( Table1[Device Start Time] ),
                Table1[Result Code] IN { "TCS", "TFS" },
                ALLEXCEPT ( Table1, Table1[AlertID] )
            )
        RETURN
            End_ - Sent_
    )
RETURN
    AVERAGEX ( AuxTable_, [ResponseTime] )

     

    Please mark the question solved when we get to the solution and consider kudoing if posts are helpful.

    Cheers  Datanaut

    • AlB's avatar
      AlB
      Icon for Community Champion rankCommunity Champion
      7 years ago

      or, equivalently, a more compact albeit probably less readable version:

      Measure =
AVERAGEX (
    DISTINCT ( Table1[AlertID] ),
    CALCULATE ( MIN ( Table1[Device Start Time] ), Table1[Result Code] IN { "TCS", "TFS" }, ALLEXCEPT ( Table1, Table1[AlertID] ))
        - CALCULATE ( MIN ( Table1[Device Start Time] ), Table1[Result Code] = "TNS", ALLEXCEPT ( Table1, Table1[AlertID] ) )
)

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi ! 

         

        Thank you for your help. What type does the calculation return? 

        I tried both methods and they give me a result of 26 864.18,

        Which seems like a rather large average response time haha