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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Anonymous
Not applicable

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

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?

2 ACCEPTED SOLUTIONS
Community Champion

@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
)``````

Anonymous
Not applicable

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])

2 REPLIES 2
Community Champion

@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
)``````

Anonymous
Not applicable

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])

## Helpful resources

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors