Update Column based on Multiple Filters

[RonitGupta]

I want to update Column [Product Family] and there are three filters while binaryAnd accepts two only.

IF BinaryAnd([Plant]="A",[Product Family]="B",[Product Category]="C") THEN "X" ELSE [Product Family] ENDIF

How to deal with that?

I have sometime more than 4 filters too. 

[Felipe_Ribeir0 · Accepted solution]

Hi @RonitGupta 

You can use the formula like this, please see if it works for you

IF [Plant]="A" AND [Product Family]="B" AND [Product Category]="C" THEN "X" ELSE [Product Family] ENDIF

[ShankerV · Accepted solution]

Hi @RonitGupta 

You need to use the IF statement without BinaryAnd.

IF [Plant]="A" AND

[Product Family]="B" AND

[Product Category]="C")

THEN "X"

ELSE [Product Family]

ENDIF

If you believe your problem has been resolved. Please mark helpful answers as a solution so that future users with the same problem can find them more easily!!!!

Many thanks

Shanker V

[RonitGupta]

Hello Felipe,

Thanks. It worked for me. 

I have cases where there are 10 product categories and I need to update only 3 based on already selected two filters. I hope OR should work along with AND.

IF [Plant]="A" AND [Product Family]="B"  AND OR([Product Category]="C", [Product Category]="D",[Product Category]="E") THEN "X" ELSE [Product Family] END IF

[ShankerV]

Hi @RonitGupta 

AND OR cannot be used at the same time, It will be an error.

[ShankerV · Accepted solution]

 @RonitGupta 

Please use the below.

IF [Plant]="A" AND [Product Family]="B" AND [Product Category]="C"
THEN "X"
elseif [Plant]="A" AND [Product Family]="B" AND [Product Category]="D"
THEN "X"
elseif [Plant]="A" AND [Product Family]="B" AND [Product Category]="E"
THEN "X"
ELSE [Product Family]
ENDIF

[Felipe_Ribeir0 · Accepted solution]

Hi @RonitGupta 

Please see if this is what you are looking for:

IF [Plant]="A" AND [Product Family]="B" AND ([Product Category]="C" OR [Product Category]="D" OR [Product Category]="E") THEN "X" ELSE [Product Family] ENDIF

[RonitGupta]

Hi,

This is not working. For product category D and E, irrespective of other criteria it give X. 

[ShankerV]

Hi @RonitGupta 

I strongly believe the below formula will give you the expected output.

Please check and confirm.

IF [Plant]="A" AND [Product Family]="B" AND [Product Category]="C"
THEN "X"
elseif [Plant]="A" AND [Product Family]="B" AND [Product Category]="D"
THEN "X"
elseif [Plant]="A" AND [Product Family]="B" AND [Product Category]="E"
THEN "X"
ELSE [Product Family]
ENDIF

I have checked with example also and shared the output.

Many thanks

Shanker V

[RonitGupta]

Hi ShankerV,

The solution provided by you is working well. Thanks a lot!

I am unable to mention someone while replying using "@".  I don't know how to mention someone on this forum.