Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    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

v-juanli-msft's avatar
v-juanli-msft
Community Support
7 years ago

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