Multiple IFs & Ands

[JamesBockett]

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?

[v-jayw-msft · Accepted solution]

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

[JamesBockett]

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]

Hi @JamesBockett ,

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

Thanks,

Pragati

[JamesBockett]

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"