March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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
Solved! Go to 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
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
My current DAX request is the following :
@Anonymous Please post the sample data that can be copied, to provide you with an accurate solution.
Proud to be a PBI Community Champion
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 |
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
129 | |
90 | |
75 | |
58 | |
53 |
User | Count |
---|---|
200 | |
104 | |
101 | |
67 | |
55 |