topic How to calculate the average of the response time per conversation ID in DAX Commands and Tips

How to calculate the average of the response time per conversation ID

Anonymous — Sun, 24 Apr 2022 16:35:02 GMT

Hi,

I have the following table with conversations in Power BI:

In DAX, I want to calculate the average of the response time per conversation ID .

Variable X = the 'created_at' variable from the first row where is_interaction = 1 (MARKED AS YELLOW)

Variable Y = the 'created_at' variable from the first row where owner_type = "Agent" and owner_id <> 1 after the first row where is_interaction = 1 (MARKED AS ORANGE)

With these variables I would like to calculate the average response time per conversation ID as a measure. As example I calculated the average response time for one conversation (MARKED AS BLUE)

Any ideas on how to achieve this?

I already have the measure to calculate the first response time:

Response Time = VAR _conversation_created_at = CALCULATE( MINX( 'table', 'table'[created_at] ), 'table'[is_interaction] = 1 ) VAR _conversation_response_created_at = CALCULATE( MINX( 'table', 'table'[created_at] ), 'table'[owner_type] = "Agent", 'table'[owner_id] <> 1, 'table'[created_at] > _conversation_created_at ) VAR _result = DATEDIFF(_conversation_created_at, _conversation_response_created_at, MINUTE) RETURN _result

P.S.: this post is a continuation of the post , credits for SpartaBI solving this problem

Re: How to calculate the average of the response time per conversation ID

SpartaBI — Sun, 24 Apr 2022 10:03:53 GMT

Hey Anonymous, just to make sure, you want it as a calculated column in your data model table and not as a measure?

Re: How to calculate the average of the response time per conversation ID

SpartaBI — Sun, 24 Apr 2022 10:08:07 GMT

Also, can you share the table in the pic as a table in a comment

Re: How to calculate the average of the response time per conversation ID

Anonymous — Sun, 24 Apr 2022 16:27:36 GMT

Hey SpartaBI , I would like it as a measure to use it in a Report.

Re: How to calculate the average of the response time per conversation ID

Anonymous — Sun, 24 Apr 2022 16:31:08 GMT

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	Traveller	153263	26276	c	2022-03-05 15:49:12	1
266750	Traveller	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	Traveller	153263	26276	i	2022-03-09 13:01:07	1
271728	Traveller	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
272319	Traveller	153263	26276	m	2022-03-10 12:04:51	1
272327	Traveller	153263	26276	n	2022-03-10 12:04:55	1
272334	Agent	20	26276	o	2022-03-10 12:05:16

Re: How to calculate the average of the response time per conversation ID

Anonymous — Sun, 24 Apr 2022 16:32:30 GMT

Hope this helps! Much thanks for helping me out! 🙂

Re: How to calculate the average of the response time per conversation ID

SpartaBI — Sun, 24 Apr 2022 18:49:16 GMT

So in that case, what columns will you bring to the report ? 🙂
Need to understand the filter context that will be applied in the measure.
Or the report (the table/matrix) actually contains all the columns from the data table you sent?

Re: How to calculate the average of the response time per conversation ID

Anonymous — Sun, 24 Apr 2022 19:34:44 GMT

Hey, this is just how the report looks like for now. Super simple, without any filters.

Later on, I would like a filter where I can select all the conversations on a Yearly and Monthy level.

The tables I sent are now the only columns I need