Use Switch and OR

[Anonymous]

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

[Anonymous · Accepted solution]

"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]

Thanks, this is exactly what i wanted. 

I was so close to the solution haha

[AnthonyTilley]

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]

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

[AnthonyTilley]

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 

[Anonymous]

Give back a TRUE if ReferredID equals 1 of the 4 desired ID's and if the match with 1 of the UserID's...

If ReferredByID equals 1073 OR 440190 OR 39050 OR 28536 

AND UserID equals 1028 OR 383575 OR 93233 OR 1013 then TRUE

else will give back FALSE everytime.