Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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
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] ) ) )
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
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. |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |