Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
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
v-juanli-msft
Community Support
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)

11.png

 

Best Regards

Maggie

View solution in original post

4 REPLIES 4
v-juanli-msft
Community Support
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)

11.png

 

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.
PattemManohar
Community Champion
Community Champion

@Anonymous 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 PBI Community Champion




Anonymous
Not applicable

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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