Calculate duration between joined table

[Anonymous]

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

[v-juanli-msft · Accepted solution]

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]

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.

[PattemManohar]

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

[Anonymous]

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