Calculate Average Response Time based on other columns

[Anonymous]

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:

AlertID	Device	Device Start Time	Result Code	Client Code
1	SMS	2019/08/04 11:06	TNS
1	SMS	2019/08/04 11:12	TCS
1			SM1
1			SMD	Message successfully delivered.
1			SMD	Message successfully delivered.
2	SMS	2019/08/04 10:00	TNS
2	SMS	2019/08/04 10:15	TNS
2		2019/08/04 10:30	TFS
2			SMD	Message successfully delivered.
2			SMD	Message successfully delivered.
3	SMS	2019/08/04 12:30	TNS
3	SMS	2019/08/04 12:45	TNS
3		2019/08/04 13:00	TCS
3			SMD	Message successfully delivered.
3			SMD	Message successfully delivered.
4	SMS	2019/08/05 11:00	TNS
4	SMS	2019/08/04 11:15	TFS
4			SMD	Message successfully delivered.
4			SMD	Message 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.

[AlB]

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  

[AlB]

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]

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

[AlB]

The result will be in days since that is the base for the Datetime type. You can then convert it to mins, secs,

With the data you provided I get an average of -0,24 days but that is because id 4 seems to be travelling back in time:

4	SMS	2019/08/05 11:00	TNS
4	SMS	2019/08/04 11:15	TFS
4			SMD	Message successfully delivered.
4			SMD	Message successfully delivered.