Forum Discussion
NOT IN CONTAINS
Hello Community -
I am using the column formula below, but it's not returning the correct results. My situation is that I have a list of SKUs that need to identified as "Standard PSUs". The characteristic of these SKUs is that they SHOULD contain a "-1" in the SKU name....but should NOT contain "CU".
My formula below seems to be doing the exact opposite. It is returning NA for SKUs that have the features defined above and I can't figure out why. Or maybe there is a better formula? Thanks in advance for any help!
Anonymous - I think you should be using FIND like this:
Standard PSUs = IF(FIND("-1",'Flu_Shipped_Query'[Item],,0)<>0 && FIND("CU-",'Flu_Shipped_Query'[Item],,0),1,0)
6 Replies
Anonymous - I think you should be using FIND like this:
Standard PSUs = IF(FIND("-1",'Flu_Shipped_Query'[Item],,0)<>0 && FIND("CU-",'Flu_Shipped_Query'[Item],,0),1,0)Greg_Deckler Thanks! That worked...but a quick question: Why give the value of 0 and 1 ? Is this so that I can convert it to a True / False column?
Greg_Deckler Hi Greg - I noticed that actually the formula seems to be returning the wrong values.
"Standard" PSUs should include the text "-1", but should NOT include "CU".
Anonymous - Must have misunderstood.
Standard PSUs = IF(FIND("-1",'Flu_Shipped_Query'[Item],,0)<>0 && FIND("CU",'Flu_Shipped_Query'[Item],,0)=0,1,0)I find that using 1 and 0 is better than using TRUE and FALSE as there are limitations with using columns that only contain boolean values.
Anonymous , try like
IF('Flu_Shipped Query'[Item] IN { "-1"} && NOT ('Flu_Shipped Query'[Item] IN { "CU-", "Standard PSUs","NA" }) , <Success> , <fail>)
- Standard PSUs 2 = IF('Flu_Shipped Query'[Item] IN { "-1"} && NOT ('Flu_Shipped Query'[Item] IN { "CU-"}) , "Standard PSU" , "NA")Hi Amit - Your formula seems to be doing the same thing my formula was doing. It is returning "NA" for items that should say Standard PSU. Any thoughts?
