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 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

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

4 Replies

  • PattemManohar's avatar
    PattemManohar
    Community Champion
    7 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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
  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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.
  • 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