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

[Anonymous]

Hi,

I have the following table with conversations in Power BI:

Filtered table of conversations where conversation_id is 26276

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 How to calculate the average of the 'first response' time per conversation ID , credits for  @SpartaBI solving this problem

[SpartaBI]

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

[Anonymous]

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

[Anonymous]

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

[SpartaBI]

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

[Anonymous]

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

[SpartaBI]

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?

[Anonymous]

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