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 |