Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

Hi,

 

I have the following table with conversations in Power BI:

 

Filtered table of conversations where conversation_id is 26276Filtered 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

7 REPLIES 7
SpartaBI
Community Champion
Community Champion

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

Anonymous
Not applicable

idowner_typeowner_idconversation_idmessagecreated_atis_interaction
260943Agent126276a2022-03-01 15:00:14 
265544Agent126276b2022-03-05 12:01:35 
266749Traveller15326326276c2022-03-05 15:49:121
266750Traveller15326326276d2022-03-05 15:49:221
266753Agent1426276e2022-03-05 15:51:09 
267003Agent126276f2022-03-06 12:01:23 
268904Agent126276g2022-03-07 12:00:53 
271141Agent126276h2022-03-09 12:00:55 
271725Traveller15326326276i2022-03-09 13:01:071
271728Traveller15326326276j2022-03-09 13:01:221
271727Agent1026276k2022-03-09 13:01:23 
272085Agent126276l2022-03-10 12:01:26 
272319Traveller15326326276m2022-03-10 12:04:511
272327Traveller15326326276n2022-03-10 12:04:551
272334Agent2026276o2022-03-10 12:05:16 
Anonymous
Not applicable

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

SpartaBI
Community Champion
Community Champion

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

Anonymous
Not applicable

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

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
Not applicable

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

 

Matrix 3.PNG

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors