Forum Discussion

StidifordN's avatar
StidifordN
Icon for Helper III rankHelper 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 re...
Need Help
  • d_gosbell's avatar
    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 

v-juanli-msft's avatar
v-juanli-msft
Icon for Community Support rankCommunity 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's avatar
StidifordN
Icon for Helper III rankHelper 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's avatar
    d_gosbell
    Icon for Super User rankSuper 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 

    • StidifordN's avatar
      StidifordN
      Icon for Helper III rankHelper III
      7 years ago

      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 

       

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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.