SWITCH function with &&

[pbrainard]

Trying to use the SWITCH function to populate a new column. Seems to be ignoring the first statement for the Landing and New Ground Bothell lines. All Landing are coming out as 15 and New Ground Bothell as 9. New Ground Avondale is okay. Can someone help me format this correctly? Thanks!

numUnits = SWITCH(TRUE(),

('All Agency FY21'[Date] <= 03/17/2020 && 'All Agency FY21'[Location]="Landing"),20,

('All Agency FY21'[Date] > 03/17/2020 &&'All Agency FY21'[Location]="Landing"),15,

('All Agency FY21'[Date] <= 03/17/2020 && 'All Agency FY21'[Location]="New Ground Bothell"),14,

('All Agency FY21'[Date] > 03/17/2020 && 'All Agency FY21'[Location]="New Ground Bothell"),9,

'All Agency FY21'[Location]="New Ground Avondale",24)

[Jos_Woolley · Accepted solution]

Hi,

Most likely because you haven't made your dates explicit. As such, an expression such as

<= 03/17/2020

will be evaluated as 'less than the continued fraction 3 divided by 17 divided by 2020', i.e. <=0.000087361677344...

Try replacing with DATEVALUE, e.g.

DATEVALUE("03/17/2020")

Regards

[pbrainard]

Thanks. Still learning!!! Works perfect.

[Jos_Woolley]

You're welcome!