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

[Anonymous]

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?

[SpartaBI · Accepted solution]

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

[SpartaBI · Accepted solution]

@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 · Accepted solution]

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