Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

From 4 Calculated Columns to 1?

Are there any common tips/tricks for combining many calculations into one?   My Scenario:   I have these 4 calculated columns used to get my final result. I would like to be able to do all the ca...
Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    Hi Anonymous,

    After research and test, I can combine the for steps to two, I am unable to do all the calculations in one step. Becasue the IF function can not be used in COUNT function.

    I get the CurrentStatus column using the formula.

    CurrentStatus = IF(Sheet1[Date]=CALCULATE(
              MAX(Sheet1[Date]),
              FILTER(Sheet1,Sheet1[EventID] = EARLIER(Sheet1[EventID])&&Sheet1[ContactID]=EARLIER(Sheet1[ContactID]))),IF(Sheet1[Status]="Accepted","Accepted",BLANK()))


    Then use your last formula to get the final result.



    Best Regards,
    Angelia

v-huizhn-msft's avatar
v-huizhn-msft
Microsoft Employee
9 years ago

Hi Anonymous,

After research and test, I can combine the for steps to two, I am unable to do all the calculations in one step. Becasue the IF function can not be used in COUNT function.

I get the CurrentStatus column using the formula.

CurrentStatus = IF(Sheet1[Date]=CALCULATE(
              MAX(Sheet1[Date]),
              FILTER(Sheet1,Sheet1[EventID] = EARLIER(Sheet1[EventID])&&Sheet1[ContactID]=EARLIER(Sheet1[ContactID]))),IF(Sheet1[Status]="Accepted","Accepted",BLANK()))


Then use your last formula to get the final result.



Best Regards,
Angelia

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Thanks, that will work. :smileyvery-happy: