Skip to main content
cancel
Showing results for 
Search instead 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

Reply
StidifordN
Helper III
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

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 

View solution in original post

6 REPLIES 6
v-juanli-msft
Community Support
Community Support

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

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.

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)

4.png

 

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.

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 

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
Helper III
Helper III

Swtich.JPG

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors