From 4 Calculated Columns to 1?

[Anonymous]

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

[v-huizhn-msft · Accepted solution]

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]

Thanks, that will work.