Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I would like to calculate the average 'service response time' per conversation id as a measure.
The 'service response time' is calculated by the difference between the 'created_at' variable from Y and X in minutes:
X = the first row where owner_type = "User" and is_interaction = 1.
Y = the first row after X where owner_type = "Agent" and owner_id <> 1
id | owner_type | owner_id | conversation_id | message | created_at | is_interaction |
260943 | Agent | 1 | 26276 | a | 2022-03-01 15:00:14 | |
265544 | Agent | 1 | 26276 | b | 2022-03-05 12:01:35 | |
266749 | User | 153263 | 26276 | c | 2022-03-05 15:49:12 | 1 |
266750 | User | 153263 | 26276 | d | 2022-03-05 15:49:22 | 1 |
266753 | Agent | 14 | 26276 | e | 2022-03-05 15:51:09 | |
267003 | Agent | 1 | 26276 | f | 2022-03-06 12:01:23 | |
268904 | Agent | 1 | 26276 | g | 2022-03-07 12:00:53 | |
271141 | Agent | 1 | 26276 | h | 2022-03-09 12:00:55 | |
271725 | User | 153263 | 26276 | i | 2022-03-09 13:01:07 | 1 |
271728 | User | 153263 | 26276 | j | 2022-03-09 13:01:22 | 1 |
271727 | Agent | 10 | 26276 | k | 2022-03-09 13:01:23 | |
272085 | Agent | 1 | 26276 | l | 2022-03-10 12:01:26 |
Any ideas on how to calculate this?
Solved! Go to Solution.
Hi @Anonymous
Here is the sample file with the solution https://www.dropbox.com/t/nRZI3zMA3aT7srRG
Following your latest clarification, aggregation by date (for example latest time per date) shall be required as part of the calculation. In order to achieve this without further complications in the code, a date column must be added.
Date =
DATE (
YEAR ( 'Service Data'[created_at] ),
MONTH ( 'Service Data'[created_at] ),
DAY ( 'Service Data'[created_at] )
)
Then our master measure would be
Response Time (minutes) =
VAR CurrentDateTime =
MAX ( 'Service Data'[created_at] )
VAR CurrentDate =
MAX ( 'Service Data'[Date] )
VAR CurrentType =
MAX ( 'Service Data'[owner_type] )
VAR CurrentConversationTable =
CALCULATETABLE (
'Service Data',
ALLEXCEPT ( 'Service Data', 'Service Data'[conversation_id] )
)
VAR CurrentDateTypeTable =
FILTER (
CurrentConversationTable,
'Service Data'[Date] = CurrentDate
&& 'Service Data'[owner_type] = CurrentType
)
VAR NextTime_DateTypeTable =
FILTER ( CurrentDateTypeTable, 'Service Data'[created_at] > CurrentDateTime )
VAR NextTime_AgentTable =
FILTER (
CurrentConversationTable,
'Service Data'[created_at] > CurrentDateTime
&& 'Service Data'[owner_type] = "Agent"
&& 'Service Data'[owner_id] <> 1
)
VAR X = CurrentDateTime
VAR Y =
MINX ( NextTime_AgentTable, 'Service Data'[created_at] )
VAR TimeDuration =
DATEDIFF ( X, Y, SECOND )
VAR Result =
DIVIDE ( TimeDuration, 60 )
RETURN
IF (
CurrentType = "User",
IF ( ISBLANK ( COUNTROWS ( NextTime_DateTypeTable ) ), Result )
)
Then depending on average of sum:
Total Response Time (minutes) =
SUMX (
VALUES ( 'Service Data'[id] ),
[Response Time (minutes)]
)
Average Response Time (minutes) =
AVERAGEX (
VALUES ( 'Service Data'[id] ),
[Response Time (minutes)]
)
Hi @Anonymous
Here is the sample file with the solution https://www.dropbox.com/t/nRZI3zMA3aT7srRG
Following your latest clarification, aggregation by date (for example latest time per date) shall be required as part of the calculation. In order to achieve this without further complications in the code, a date column must be added.
Date =
DATE (
YEAR ( 'Service Data'[created_at] ),
MONTH ( 'Service Data'[created_at] ),
DAY ( 'Service Data'[created_at] )
)
Then our master measure would be
Response Time (minutes) =
VAR CurrentDateTime =
MAX ( 'Service Data'[created_at] )
VAR CurrentDate =
MAX ( 'Service Data'[Date] )
VAR CurrentType =
MAX ( 'Service Data'[owner_type] )
VAR CurrentConversationTable =
CALCULATETABLE (
'Service Data',
ALLEXCEPT ( 'Service Data', 'Service Data'[conversation_id] )
)
VAR CurrentDateTypeTable =
FILTER (
CurrentConversationTable,
'Service Data'[Date] = CurrentDate
&& 'Service Data'[owner_type] = CurrentType
)
VAR NextTime_DateTypeTable =
FILTER ( CurrentDateTypeTable, 'Service Data'[created_at] > CurrentDateTime )
VAR NextTime_AgentTable =
FILTER (
CurrentConversationTable,
'Service Data'[created_at] > CurrentDateTime
&& 'Service Data'[owner_type] = "Agent"
&& 'Service Data'[owner_id] <> 1
)
VAR X = CurrentDateTime
VAR Y =
MINX ( NextTime_AgentTable, 'Service Data'[created_at] )
VAR TimeDuration =
DATEDIFF ( X, Y, SECOND )
VAR Result =
DIVIDE ( TimeDuration, 60 )
RETURN
IF (
CurrentType = "User",
IF ( ISBLANK ( COUNTROWS ( NextTime_DateTypeTable ) ), Result )
)
Then depending on average of sum:
Total Response Time (minutes) =
SUMX (
VALUES ( 'Service Data'[id] ),
[Response Time (minutes)]
)
Average Response Time (minutes) =
AVERAGEX (
VALUES ( 'Service Data'[id] ),
[Response Time (minutes)]
)
True champ!
I aggregated the date with a 5 minutes timestamp.
This is the solution! Many thanks for the effort!
DATE = DATEVALUE ( 'Service Data'[created_at] ) + TIME ( HOUR ( 'Service Data'[created_at] ), MINUTE ( 'Service Data'[created_at] ) - MOD ( MINUTE ( 'Service Data'[created_at] ), 5 ), 0 )
Good job 👍
Hi @Anonymous
Here is a sample file with the solution https://www.dropbox.com/t/eefOsChPez2kmESO
Based on your description and the provided sample data, the time of both message "c" & message "d" will be subtracted from the time of message "e".
Similarly the time of both message "i" & message "j" will be subtracted from the time of message "k".
Average service response time (minutes) =
AVERAGEX (
VALUES ('Service Data'[id] ),
CALCULATE (
IF (
MAX ( 'Service Data'[owner_type] ) = "User",
VAR X = MAX ( 'Service Data'[created_at] )
VAR FilteredTable =
FILTER (
CALCULATETABLE (
'Service Data',
ALLEXCEPT ( 'Service Data', 'Service Data'[conversation_id] )
),
'Service Data'[created_at] > X && 'Service Data'[owner_type] = "Agent" && 'Service Data'[owner_id] <> 1
)
VAR Y = MINX ( FilteredTable, 'Service Data'[created_at] )
VAR TimeDuration = DATEDIFF ( X, Y, SECOND )
VAR Result = DIVIDE ( TimeDuration, 60 )
RETURN
Result
)
)
)
Total service response time (minutes) =
SUMX (
VALUES ('Service Data'[id] ),
CALCULATE (
IF (
MAX ( 'Service Data'[owner_type] ) = "User",
VAR X = MAX ( 'Service Data'[created_at] )
VAR FilteredTable =
FILTER (
CALCULATETABLE (
'Service Data',
ALLEXCEPT ( 'Service Data', 'Service Data'[conversation_id] )
),
'Service Data'[created_at] > X && 'Service Data'[owner_type] = "Agent" && 'Service Data'[owner_id] <> 1
)
VAR Y = MINX ( FilteredTable, 'Service Data'[created_at] )
VAR TimeDuration = DATEDIFF ( X, Y, SECOND )
VAR Result = DIVIDE ( TimeDuration, 60 )
RETURN
Result
)
)
)
Hey @tamerj1 ,
Many thanks for helping me out! Getting really close now... I only want the calculate the 'service response time' of the latest row where owner_type = "User".
So the rows with 'id' 266749 and 271725 must be blank.
Any ideas on how to fix this? I think I should add an index column combined with the earlier function for this, but I would like to hear your approach 🙂
@Anonymous
Yea can be done without index column but not today 😅
will get back to you tomorrow
Awesome, I was trying to find a solution but I'm stuck. It's a tough one for me
It is indeed
Awesome, I was trying to find a solution but I'm stuck. It's a tough one for me
User | Count |
---|---|
54 | |
22 | |
19 | |
16 | |
11 |
User | Count |
---|---|
81 | |
55 | |
39 | |
20 | |
12 |