Calculating a time difference based on two times spread over two tables

[Tim_Groothuis]

Hi there,

I'm fairly new to PowerBI, so it might be a dumb question. I have the following tables:

Sentinel-incidents contain security incidents from our system. A single incident might have one or more comments and tasks associated with it. I've added a one-to-many relation from the sentinel-incidents table on the IncidentName (which is a unique value). We have a system which automatically adds a sentinel-task with the title "ACKNOWLEDGED" to an incident once we've begun working on it.

I'd like to get for each Sentinel-Incident the associated Sentinel-task the title "ACKNOWLEDGED", and get the time difference between the CreatedTimeUtc of the sentinel-incident and the CreatedTimeUtc of the sentinel-task. That'll serve as our response time and ties into our SLA.

Currently, I tried to add a column to the sentinel-incidents table using the following query. The query isn't working and probably quite wrong, so all help is appreciated.

Thanks in advance!

[amitchandak · Accepted solution]

@Tim_Groothuis , Try a measure like

AverageX('Sentinel-incidents', datediff(min('Sentinel-task'[CreatedTimeutc]), max('Sentinel-comment'[CreatedTimeutc]), MINUTE))

[Tim_Groothuis]

Hi @amitchandak,
Awesome! That's exactly what I'm looking for, thanks for the help!