Forum Discussion
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...
Hi Anonymous
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)Best Regards
Maggie
Anonymous Please post the sample data that can be copied, to provide you with an accurate solution.
Here is a sample :
Table Case :
Date K_Case 16/11/2018 09:33:43 1 11/11/2018 11:22:43 2 13/11/2018 10:50:12 2 13/11/2018 09:45:45 2 15/11/2018 09:23:43 3 15/11/2018 08:25:02 4 20/11/2018 08:21:32 4 Table Action:
Date K_Action 16/11/2018 11:23:53 45 12/11/2018 13:33:43 11 13/11/2018 14:50:12 17 13/11/2018 15:45:45 16 16/11/2018 09:23:43 12 16/11/2018 08:25:02 13 20/11/2018 15:21:32 14 Table Case_Action:
K_Case K_Action 1 45 2 11 2 17 2 16 3 12 4 13 4 14