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.

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

Forum Discussion

StidifordN

Helper III

7 years ago

Solved

Switch with &&& and ||

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.

Need Help

d_gosbell
7 years ago
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

6 Replies

StidifordN
Helper III
7 years ago
v-juanli-msft
Community Support
7 years ago
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
  Helper III
  7 years ago
  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.
  - d_gosbell
    Super User
    7 years ago
    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