cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

## 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.

1 ACCEPTED SOLUTION
Super User

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 6
Community Support

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

Helper III

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.

Community Support

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.

Super User

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

Helper III

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

Helper III