cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Anonymous
Not applicable

## 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
Community Support

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

4 REPLIES 4
Community Support

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
Not applicable

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 Champion

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

Proud to be a PBI Community Champion

Anonymous
Not applicable

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

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors