cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ellana Frequent Visitor
Frequent Visitor

Calculate duration between joined table

Hi all,

 

I have 3 tables :

 

Case

- create_date

 

Action

- create_date

 

and the join table

 

Case_join

- action_id

- case_id

 

Case and Action have a one to many relationship.

 

I am trying to calculate the duration between Case[create_date] and the first Action[create_date] associated to the case.

 

I tried to add a new column in the join table and use a DAX query but I didn't manage to get it right.

 

Any hints ?

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Calculate duration between joined table

Hi @ellana

Create measures in Table "Case"

Measure to return the first Action[create_date] associated to each case

min per case = CALCULATE(MIN('Action'[Date]),ALLEXCEPT(Case_Action,Case_Action[K_Case]))

Measure to calculate the duration between Case[create_date] and the first Action[create_date] associated to the case

Take this formula for example

the first Action[create_date] - Case[create_date]  per row

duration_day = DATEDIFF(MAX('Case'[Date]),[min per case],DAY)

duration_hours = DATEDIFF(MAX('Case'[Date]),[min per case],HOUR)

11.png

 

Best Regards

Maggie

4 REPLIES 4
Super User
Super User

Re: Calculate duration between joined table

@ellana Please post the sample data that can be copied, to provide you with an accurate solution.



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





ellana Frequent Visitor
Frequent Visitor

Re: Calculate duration between joined table

Here is a sample :

 

Table Case :

 

DateK_Case
16/11/2018 09:33:431
11/11/2018 11:22:432
13/11/2018 10:50:122
13/11/2018 09:45:452
15/11/2018 09:23:433
15/11/2018 08:25:024
20/11/2018 08:21:324

 

 

Table Action:

DateK_Action
16/11/2018 11:23:5345
12/11/2018 13:33:4311
13/11/2018 14:50:1217
13/11/2018 15:45:4516
16/11/2018 09:23:4312
16/11/2018 08:25:0213
20/11/2018 15:21:3214

 

 

Table Case_Action:

K_CaseK_Action
145
211
217
216
312
413
414
ellana Frequent Visitor
Frequent Visitor

Re: Calculate duration between joined table

My current DAX request is the following :

 

CALCULATE (
CALCULATE (
MIN ( 'Action'[D_CREATE] );
FILTER ( 'Action'; 'Action'[D_CREATE] = MIN ( 'Action'[D_CREATE] ) )
)
)
 
The problem is it only return the first Action created in the table, and not for every row the first action.
Community Support Team
Community Support Team

Re: Calculate duration between joined table

Hi @ellana

Create measures in Table "Case"

Measure to return the first Action[create_date] associated to each case

min per case = CALCULATE(MIN('Action'[Date]),ALLEXCEPT(Case_Action,Case_Action[K_Case]))

Measure to calculate the duration between Case[create_date] and the first Action[create_date] associated to the case

Take this formula for example

the first Action[create_date] - Case[create_date]  per row

duration_day = DATEDIFF(MAX('Case'[Date]),[min per case],DAY)

duration_hours = DATEDIFF(MAX('Case'[Date]),[min per case],HOUR)

11.png

 

Best Regards

Maggie