Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more

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 logo


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
PBIApril_Carousel

Power BI Monthly Update - April 2025

Check out the April 2025 Power BI update to learn about new features.

Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

April2025 Carousel

Fabric Community Update - April 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors