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.

Reply
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:

 

image.PNG

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
SpartaBI
Community Champion
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
	)

 

View solution in original post

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

View solution in original post

2 REPLIES 2
SpartaBI
Community Champion
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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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