Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 How to calculate the average of the 'first response' time per conversation ID , credits for @SpartaBI solving this problem
Also, can you share the table in the pic as a table in a comment
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 |
Hope this helps! Much thanks for helping me out! 🙂
Hey @Anonymous, just to make sure, you want it as a calculated column in your data model table and not as a measure?
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?
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
User | Count |
---|---|
42 | |
28 | |
24 | |
20 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |