cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KassBroon Frequent Visitor
Frequent Visitor

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
MoOv Regular Visitor
Regular Visitor

Re: Use Switch and OR

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

View solution in original post

6 REPLIES 6
AnthonyTilley Established Member
Established Member

Re: Use Switch and OR

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

Power bi Switch.png
 
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
KassBroon Frequent Visitor
Frequent Visitor

Re: Use Switch and OR

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

AnthonyTilley Established Member
Established Member

Re: Use Switch and OR

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 

 

 

KassBroon Frequent Visitor
Frequent Visitor

Re: Use Switch and OR

 

Example.PNG

 

 

 

 

 

 

 

 

 

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. 

MoOv Regular Visitor
Regular Visitor

Re: Use Switch and OR

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

View solution in original post

Highlighted
KassBroon Frequent Visitor
Frequent Visitor

Re: Use Switch and OR

Thanks, this is exactly what i wanted. 

I was so close to the solution haha

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 44 members 857 guests
Please welcome our newest community members: