Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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 |
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
111 | |
105 | |
105 | |
88 | |
61 |
User | Count |
---|---|
165 | |
133 | |
132 | |
95 | |
86 |