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

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

Anonymous — Thu, 21 Apr 2022 11:30:15 GMT

Hi,

I have the following table in Power BI:

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

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

Variable Y = the 'created_at' variable from the first row where owner_type = "Agent" after the first row where is_interaction = 1 (MARKED AS YELLOW)

With these variables I would like to calculate the difference from Y and X from each 'conversation_id', preferably in minutes (MARKED AS BLUE).

Any ideas on how to achieve this?

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

SpartaBI — Thu, 21 Apr 2022 12:54:47 GMT

Anonymous

Response Time = VAR _conversion_id = MAX('Table'[conversion_id]) VAR _conversation_created_at = CALCULATE( MINX( 'Table', 'Table'[created_at] ), REMOVEFILTERS('Table'), 'Table'[conversion_id] = _conversion_id, 'Table'[owner_type] = "Traveller", 'Table'[is_interaction] = 1 ) VAR _conversation_response_created_at = CALCULATE( MINX( 'Table', 'Table'[created_at] ), REMOVEFILTERS('Table'), 'Table'[conversion_id] = _conversion_id, 'Table'[owner_type] = "Agent" ) VAR _result = FORMAT( _conversation_response_created_at - _conversation_created_at, "hh:nn:ss" ) RETURN IF( 'Table'[owner_type] = "Agent" && 'Table'[created_at] = _conversation_response_created_at && NOT ISBLANK(_conversation_created_at), _result )

In case (I assume from the business logic I see in your table) there can't be 1 in 'Table'[is_interaction] unless an agent has actually responed, and there can't be a situation where an agent repsoneded without a traveller starting a request you can actually remove some redundent lines of code to make it like this:

Response Time = VAR _conversion_id = MAX('Table'[conversion_id]) VAR _conversation_created_at = CALCULATE( MINX( 'Table', 'Table'[created_at] ), REMOVEFILTERS('Table'), 'Table'[conversion_id] = _conversion_id, 'Table'[owner_type] = "Traveller" ) VAR _conversation_response_created_at = CALCULATE( MINX( 'Table', 'Table'[created_at] ), REMOVEFILTERS('Table'), 'Table'[conversion_id] = _conversion_id, 'Table'[owner_type] = "Agent" ) VAR _result = FORMAT( _conversation_response_created_at - _conversation_created_at, "hh:nn:ss" ) RETURN IF( 'Table'[owner_type] = "Agent" && 'Table'[created_at] = _conversation_response_created_at, _result )

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

Anonymous — Sat, 23 Apr 2022 10:08:14 GMT

Thank your for pointing me in the right direction! I changed your measure to the following:

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

With this measure, I created a new measure to calculate the average of all conversations:

Response Time (AVG) = AVERAGEX(VALUES('table'[conversation_id]), [Response Time])