Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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?

Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

     

    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

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

     

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Anonymous 

       

      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's avatar
    Pragati11
    Super User
    4 years ago

    Hi Anonymous ,

     

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

     

    Thanks,

    Pragati

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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"