topic Re: How to calculate 'service response time' per conversation id in DAX Commands and Tips

How to calculate 'service response time' per conversation id

Anonymous — Tue, 26 Apr 2022 12:41:23 GMT

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?

Re: How to calculate 'service response time' per conversation id

tamerj1 — Thu, 28 Apr 2022 04:14:53 GMT

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 ) ) )

Re: How to calculate 'service response time' per conversation id

Anonymous — Thu, 28 Apr 2022 10:05:06 GMT

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 🙂

Re: How to calculate 'service response time' per conversation id

tamerj1 — Thu, 28 Apr 2022 10:20:20 GMT

Anonymous

Yea can be done without index column but not today 😅

will get back to you tomorrow

Re: How to calculate 'service response time' per conversation id

Anonymous — Thu, 28 Apr 2022 14:56:32 GMT

Awesome, I was trying to find a solution but I'm stuck. It's a tough one for me

Re: How to calculate 'service response time' per conversation id

Anonymous — Thu, 28 Apr 2022 14:56:33 GMT

Awesome, I was trying to find a solution but I'm stuck. It's a tough one for me

Re: How to calculate 'service response time' per conversation id

tamerj1 — Thu, 28 Apr 2022 15:39:50 GMT

It is indeed

Re: How to calculate 'service response time' per conversation id

tamerj1 — Fri, 29 Apr 2022 04:25:53 GMT

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)] )

Re: How to calculate 'service response time' per conversation id

Anonymous — Fri, 29 Apr 2022 08:23:16 GMT

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 )

Re: How to calculate 'service response time' per conversation id

tamerj1 — Fri, 29 Apr 2022 09:43:02 GMT

Good job 👍