Forum Discussion
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 calculations in one step ( So I don't have to store everything!). My first thought was just to make each step a variable, but I ran into problems doing that. I cant pass in variables as arguments when the functions take columns.
The "final result" column is the count of "Accepted" for each ContactID, but this should only include the Current Status which is based on the Max Date of each ECID (EventID + ContactID)
Here are the 4 Calculations:
ECID = Sheet1[EventID] & Sheet1[ContactID]
DateNeeded = CALCULATE(
MAX(Sheet1[Date]),
FILTER(Sheet1,Sheet1[ECID] = EARLIER(Sheet1[ECID]) ))
CurrentStatus = IF(Sheet1[Date] = Sheet1[DateNeeded],
IF(Sheet1[Status] = "Accepted",
"Accepted",
BLANK()))
FinalResult = CALCULATE(
COUNT(Sheet1[CurrentStatus]),
FILTER(Sheet1,Sheet1[ContactID] = EARLIER(Sheet1[ContactID])
))And the Table:
| EventID | ContactID | Status | Date | Column | ECID | DateNeeded | CurrentStatus | FinalResult |
| Event1 | A | Accepted | Friday, January 01, 2016 | 4 | Event1A | 1/3/2016 0:00 | 4 | |
| Event1 | A | Cancelled | Saturday, January 02, 2016 | 4 | Event1A | 1/3/2016 0:00 | 4 | |
| Event1 | A | Accepted | Sunday, January 03, 2016 | 4 | Event1A | 1/3/2016 0:00 | Accepted | 4 |
| Event2 | A | Accepted | Monday, January 04, 2016 | 4 | Event2A | 1/4/2016 0:00 | Accepted | 4 |
| Event3 | A | Accepted | Tuesday, January 05, 2016 | 4 | Event3A | 1/5/2016 0:00 | Accepted | 4 |
| Event4 | A | Accepted | Wednesday, January 06, 2016 | 4 | Event4A | 1/6/2016 0:00 | Accepted | 4 |
| Event1 | B | Accepted | Thursday, January 07, 2016 | 4 | Event1B | 1/11/2016 0:00 | 2 | |
| Event2 | B | Accepted | Friday, January 08, 2016 | 4 | Event2B | 1/12/2016 0:00 | 2 | |
| Event3 | B | Accepted | Saturday, January 09, 2016 | 4 | Event3B | 1/9/2016 0:00 | Accepted | 2 |
| Event4 | B | Accepted | Sunday, January 10, 2016 | 4 | Event4B | 1/10/2016 0:00 | Accepted | 2 |
| Event1 | B | Cancelled | Monday, January 11, 2016 | 4 | Event1B | 1/11/2016 0:00 | 2 | |
| Event2 | B | Cancelled | Tuesday, January 12, 2016 | 4 | Event2B | 1/12/2016 0:00 | 2 | |
| Event1 | C | Accepted | Wednesday, January 13, 2016 | 1 | Event1C | 1/14/2016 0:00 | ||
| Event1 | C | Cancelled | Thursday, January 14, 2016 | 1 | Event1C | 1/14/2016 0:00 |
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
2 Replies
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