Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Use Switch and OR

Hi, 

 

Ive got a problem. I want to get multiple rules in a switch statement.. But its not working, only if u use OR-statement i can add 1 more rule..

 

Sample of the data/formula(its anonymized):

TEST =
OR(SWITCH(
       TRUE(),
        SessionDetails[ReferredById]<>1073, 0,
        SessionDetails[User1Id] IN {1028,383575,93233,1013,259006,274202,361428,1011,262816} ||
    SessionDetails[User2Id] IN {1028,383575,93233,1013,259006,274202,361428,1011,262816}, 1,
        0
),  SWITCH(
       TRUE(),
        SessionDetails[ReferredById]<>231049, 0,
        SessionDetails[User1Id] IN {1028,383575,93233,1013,259006,274202,361428,1011,262816} ||
     SessionDetails[User2Id] IN {1028,383575,93233,1013,259006,274202,361428,1011,262816},
1,0
), SWITCH(
       TRUE(),
        SessionDetails[ReferredById]<>440190, 0,
        SessionDetails[User1Id] IN {1028,383575,93233,1013,259006,274202,361428,1011,262816} ||
        SessionDetails[User2Id] IN {1028,383575,93233,1013,259006,274202,361428,1011,262816}, 1,0
), SWITCH(
       TRUE(),
        SessionDetails[ReferredById]<>39050, 0,
        SessionDetails[User1Id] IN {1028,383575,93233,1013,259006,274202,361428,1011,262816} ||
        SessionDetails[User2Id] IN {1028,383575,93233,1013,259006,274202,361428,1011,262816},1,0)
 
There must be a better way, like :
TEST =
OR(SWITCH(
       TRUE(),
        SessionDetails[ReferredById]<>(1073,39050,440190,231049) 0,
        SessionDetails[User1Id] IN {1028,383575,93233,1013,259006,274202,361428,1011,262816} ||
    SessionDetails[User2Id] IN {1028,383575,93233,1013,259006,274202,361428,1011,262816}, 1,
        0)
 
To put all the "RefferedByID's" in the same line.. Please help
 
Thanks in advanced

 

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago
    "There must be a better way, like :
    TEST =
    OR(SWITCH(
           TRUE(),
            SessionDetails[ReferredById]<>(1073,39050,440190,231049) 0,
            SessionDetails[User1Id] IN {1028,383575,93233,1013,259006,274202,361428,1011,262816} ||
        SessionDetails[User2Id] IN {1028,383575,93233,1013,259006,274202,361428,1011,262816}, 1,
            0)"
     
    Try
     
    TEST =
    SWITCH(
           TRUE(),
            NOT ( SessionDetails[ReferredById] IN { 1073,39050,440190,231049 } ), 0,
            SessionDetails[User1Id] IN {1028,383575,93233,1013,259006,274202,361428,1011,262816} ||
        SessionDetails[User2Id] IN {1028,383575,93233,1013,259006,274202,361428,1011,262816}, 1,
            0)
     

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago
    "There must be a better way, like :
    TEST =
    OR(SWITCH(
           TRUE(),
            SessionDetails[ReferredById]<>(1073,39050,440190,231049) 0,
            SessionDetails[User1Id] IN {1028,383575,93233,1013,259006,274202,361428,1011,262816} ||
        SessionDetails[User2Id] IN {1028,383575,93233,1013,259006,274202,361428,1011,262816}, 1,
            0)"
     
    Try
     
    TEST =
    SWITCH(
           TRUE(),
            NOT ( SessionDetails[ReferredById] IN { 1073,39050,440190,231049 } ), 0,
            SessionDetails[User1Id] IN {1028,383575,93233,1013,259006,274202,361428,1011,262816} ||
        SessionDetails[User2Id] IN {1028,383575,93233,1013,259006,274202,361428,1011,262816}, 1,
            0)
     
    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thanks, this is exactly what i wanted. 

      I was so close to the solution haha

  • AnthonyTilley's avatar
    AnthonyTilley
    Solution Sage
    7 years ago

    I have a template i use for adding a switch with muliple criteria 

     

    you can replace the && with a || to get an or statment. and just use as many colunms as is needed 

    test_Switch = SWITCH(TRUE(),
    ([Column1]=1 && [Column2]=2 && [Column3]=1 && Table1[Column4]=1), "option 1",
    ([Column1]=1 && [Column2]=4 && [Column3]=2 && Table1[Column4]=4), "option 2",
    ([Column1]=2 && [Column2]=5 && [Column3]=5 && Table1[Column4]=5), "option 3",
    ([Column1]=2 && [Column2]=8 && [Column3]=4 && Table1[Column4]=8),"option 4",
    ([Column1]=3 && [Column2]=7 && [Column3]=8 && Table1[Column4]=5), "option 5",
    "option Default"
    )


     
    this is how i would attapt it for your needs
     
    test_Switch = SWITCH(TRUE(),
    (NOT Table1[Column4] in {1073,39050,440190,231049}), "Refered by Id option",
    ([Column1] in {1028,383575,93233,1013,259006,274202,361428,1011,262816} || [Column2] in {1028,383575,93233,1013,259006,274202,361428,1011,262816}), "option 1",
    "option Default"
    )
     
    just match this to your colunms and add the out oputs you require
    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thanks for your reply, but this isnt working for me. Doesnt give the output i need..

      • AnthonyTilley's avatar
        AnthonyTilley
        Solution Sage
        7 years ago

        Are you able to give some sample data showing the data you have and your desired outcome 

         

        If you have a PBIX file that i could down load i can try and impliment this for you