cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JamesBockett
Helper I
Helper I

Multiple IFs & Ands

Hi Everyone

 

I am trying to create a conditional column that checks a number of criteria and, depending on the answer to those queries, gives a result.

 

To give some info, I have a table containing [column A] and [column B]. I need to ask these if statements:

If [column A] equals "1" or "2" or "3" or "4" and [column B] equals "X" or "Y" I want it to return "OK"

If [column A] equals "1" or "2" or "3" or "4" and [column B] does not equal "X" or "Y" I want it to return "Check".

If [column A] does not equal "1" or "2" or "3" or "4" and [column B] does not equal "X" or "Y" I want it to return "OK"

If [column A] does not equal "1" or "2" or "3" or "4" and [column b] equals "X", or "Y" I want it to return "Check"

 

 

I have tried various IF statements with a mixture of && or || but none seem to yield the desired result. I have two questions:

1) Is it possible?

2) How is the best way to do it, without creating other conditional columns before hand?

1 ACCEPTED SOLUTION
v-jayw-msft
Community Support
Community Support

Hi @JamesBockett ,

 

You could use " in {1,2,3,4} " instead of using multiple equals.
For example:

column = IF(
			([columnA] in {1,2,3,4}&&[columnB] in {"x","y"}) || ([columnA] not in {1,2,3,4}&&[columnB] not in {"x","y"}),
				"OK",
					if(
						([columnA] in {1,2,3,4}&&[columnB] not in {"x","y"}) || ([columnA] not in {1,2,3,4}&&[columnB] in {"x","y"}),
							"check") )

Or you could use switch() function.

Switch(true(), conditon, value, conditon, value)

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

4 REPLIES 4
v-jayw-msft
Community Support
Community Support

Hi @JamesBockett ,

 

You could use " in {1,2,3,4} " instead of using multiple equals.
For example:

column = IF(
			([columnA] in {1,2,3,4}&&[columnB] in {"x","y"}) || ([columnA] not in {1,2,3,4}&&[columnB] not in {"x","y"}),
				"OK",
					if(
						([columnA] in {1,2,3,4}&&[columnB] not in {"x","y"}) || ([columnA] not in {1,2,3,4}&&[columnB] in {"x","y"}),
							"check") )

Or you could use switch() function.

Switch(true(), conditon, value, conditon, value)

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

Hi @v-jayw-msft 

 

The in function worked although, not in wasn't a supported operator. Instead, I used

 

column = IF(
([columnA] in {1,2,3,4}&&[columnB] in {"x","y"}) || not([columnA] in {1,2,3,4}not[columnB] in {"x","y"}),
"OK",
if(
([columnA] in {1,2,3,4}&&not[columnB] in {"x","y"}) || not([columnA] in {1,2,3,4}&&[columnB] in {"x","y"}),
"check") )

 

Looks like it has had the desired effect though, thanks for your support.

Pragati11
Super User
Super User

Hi @JamesBockett ,

 

Can you share what logic you have used in Power BI which is not working?

 

Thanks,

Pragati

Best Regards,

Pragati Jain

MVP logoDACertAzCert

LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

Hi @Pragati11

 

I've tried a few but to give you an idea, I have tried:

  • =IF( [column A] = "1" && [column B] ="X" || [column A] ="1" && [column B] = "Y" || [column A] = "2" && [column B] = "X" || [column A] = "2" && [column B] = "Y" and so on until all the needed statements have been entered...

 

  • =IF([column A] = "1" || [column A] = "2" || [column A] = "3" || [column A] = "4" && [column B] = "X" || [column B] = "Y" etc...

 

I don't know if it helps but, values 1, 2, 3 and 4 are a variation of the same value. so 1 is "1-a", 2 is "1-b", 3 is "1-c", 3 is "1-d". Values "X" and "Y" are actual X is blank and Y is "NONE"

 

 

Helpful resources

Announcements
Exciting changes

Power BI Community Changes

Check out the changes to the Power BI Community announced at Build.

May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Kudo Data Story carousel

Data Stories Gallery

Visit our Data Stories Gallery and give kudos to your favorite Data Stories.

Top Solution Authors