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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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