Switch with && and ||

[StidifordN]

Can a Switch include both && and ||?

Looking for a rule that looks at a the [1. ### V2] (Jan through to Dec) field and if it is 3 or 5 for all 12 fields.   If any of the 12 are not 3 or 5 then return 0, otherwise 1.

CY Activity = SWITCH(
TRUE(),
[1. Jan v2] = 3 || [1. Jan v2] = 5 &&
[1. Feb v2] = 3 || [1. Feb v2] = 5 , 1,
0)

I intend to extend this formula out to a Dec field, but the above isn't working per the below.
for reference - 3 is 'no spend' and grey in the image.  5 is the 'Future' and white in the image.

[v-juanli-msft]

Hi @StidifordN 

Is [1. Jan v2] a column as below?

c	J	F	M	A
1	1	1	1	1
2	1	1	1	1
3	2	1	2	1
4	2	2	2	2
5	3	2	3	3
6	4	4	1	4
7	4	4	4	4
8	3	3	1	3
9	4	4	4	1
10	3	1	3	1
11	4	4	2	4
12	5	5	5	1

Best Regards
Maggie

[StidifordN]

No, its a measure of nested if statements to result in numbers between 0 and 5 (excluding 2... can't remember why I did that).

I know I can do this a long way, was hoping for a more efficient version.

For each month I could do the below and it works.  Was hoping the && might work.

CY Activity Jan = CALCULATE(SWITCH(
TRUE(),
[1. Jan v2] = 3 || [1. Jan v2] = 5 , 1,
0))

CY Activity Feb = CALCULATE(SWITCH(
TRUE(),
[1. Feb v2] = 3 || [1. Feb v2] = 5 , 1,
0))

And then do another that looks to all 12 for the resulting 1.

[v-juanli-msft]

Hi @StidifordN 

If you want "CY Activity" measure to meet all conditions, 

for example, 

condition 1: [Measure 1]=3||[Measure 1]=5

condition2:  [Measure 2]=3||[Measure 2]=5

this measure would meet two conditions

cy = SWITCH(TRUE(),([Measure 1]=3||[Measure 1]=5)&&([Measure 2]=3||[Measure 2]=5),1,0)

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[d_gosbell · Accepted solution]

I think this might just be an order of operations issue I think that the logical AND is evaluated first, then the OR.

So effectively you have:  condition1 || ( condition2 && condition3) || condition4 

Whereas I think you want: (condition1 || condition2 ) && (condition3 || condition4)

I think adding extra brackets to force the order of operation you want might fix this:

CY Activity = SWITCH(
TRUE(),
([1. Jan v2] = 3 || [1. Jan v2] = 5 ) &&
([1. Feb v2] = 3 || [1. Feb v2] = 5 ) , 1,
0)

 I often use extra bracket if I have multiple logical AND/OR combinations to clarify the ordering 

[StidifordN]

Champion!

---

@d_gosbell wrote:

I think this might just be an order of operations issue I think that the logical AND is evaluated first, then the OR.

So effectively you have:  condition1 || ( condition2 && condition3) || condition4 

Whereas I think you want: (condition1 || condition2 ) && (condition3 || condition4)

I think adding extra brackets to force the order of operation you want might fix this:

CY Activity = SWITCH(
TRUE(),
([1. Jan v2] = 3 || [1. Jan v2] = 5 ) &&
([1. Feb v2] = 3 || [1. Feb v2] = 5 ) , 1,
0)

 I often use extra bracket if I have multiple logical AND/OR combinations to clarify the ordering 

---

[StidifordN]