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

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.