Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Subtract two columns based on a third id Column

Hello, 

 

How would you reccomend finding the difference of 1st & 2nd event age? 

 

Each ID has a 1st event age, and if event 1 fails, another row with the same ID has a 2nd event age. 

 

 

 

 

I have been trying to do this with a calculated column, but feel a measure might be more suitable? What do you think?

 

  • ribisht17's avatar
    ribisht17
    4 years ago

    Anonymous 

     

    Are you looking for something like this ?

     

    Step 1

    Max Event Age = CALCULATE(MAX(ColumnDiff[1 Event Age]),ALLEXCEPT(ColumnDiff,ColumnDiff[ID]))
     
    Step 2
    Min 2Event Age = CALCULATE(MIN(ColumnDiff[2 Event Age]),ALLEXCEPT(ColumnDiff,ColumnDiff[ID]))
     
    Step 3
    Diff = ColumnDiff[Max 1Event Age]-ColumnDiff[Min 2Event Age]
     

    You did not mention about the output for which thre is no failure ?

     

    Regards,

    Ritesh

2 Replies

  • ribisht17's avatar
    ribisht17
    Icon for Super User rankSuper User
    4 years ago

    Anonymous 

     

    Are you looking for something like this ?

     

    Step 1

    Max Event Age = CALCULATE(MAX(ColumnDiff[1 Event Age]),ALLEXCEPT(ColumnDiff,ColumnDiff[ID]))
     
    Step 2
    Min 2Event Age = CALCULATE(MIN(ColumnDiff[2 Event Age]),ALLEXCEPT(ColumnDiff,ColumnDiff[ID]))
     
    Step 3
    Diff = ColumnDiff[Max 1Event Age]-ColumnDiff[Min 2Event Age]
     

    You did not mention about the output for which thre is no failure ?

     

    Regards,

    Ritesh

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Ah yes! Thank you, very clean way to do it.