Replacing OR condition with IN operator and ContainsString

[monojchakrab]

Hey good people,

I am working with a code as follows :

Form = 
SWITCH(TRUE(),
    CONTAINSSTRING('summary table'[Title],"tablets"),
    "Tablets",
    CONTAINSSTRING('summary table'[Title], "sachets")||CONTAINSSTRING('summary table'[Title], "sachet"),
    "Sachets",
    CONTAINSSTRING('summary table'[Title],"jar"),
    "Jar",
    CONTAINSSTRING('summary table'[Title],"pouch"),
    "Pouch",
blank()
)

While this code works fine, it starts getting cumbersome when there are more conditions to be checked.

I was trying to use the IN operator to replace the mutiple OR conditions....something like :

CONTAINSSTRING(...[Title], IN {"Sachets","Sachet"}),

but this piece does not seem to work as it says "unexpected operator", pointing to the IN probably...

Cannot the IN operator be used in such cases?

Any help much appreciated as this will save me a lot of trouble of typing numerous OR statements

Best regds.,

[Ashish_Mathur]

Hi,

You should ideally solve this with a calculated column formula in the 'Summary Table' table.  Create another table (Table2) with a single column (Words) which should have Tablets, Sachets, Jar and Pouch.  Write this calculated column formula in the 'Summary Table' table

=FIRSTNONBLANK(FILTER(VALUES('Table2'[Words]),SEARCH('Table2'[Words],'summary table'[title],
1,0)),1)

Hope this helps.

[rsbin]

@monojchakrab ,

Will something like this work for you?

Form = 
SWITCH(TRUE(),
    CONTAINSSTRING('summary table'[Title],"tablets"),
    "Tablets",
    [Title] IN{ "Sachets", "Sachet" }, "Sachets",
    CONTAINSSTRING('summary table'[Title],"jar"),
    "Jar",
    CONTAINSSTRING('summary table'[Title],"pouch"),
    "Pouch",
blank()
)

 Regards,

[monojchakrab]

Hey @rsbin - thanks for the quick revert....but that does not seem to work.

I adapted your code on another table and column as follows :

Active with IN = 
SWITCH(TRUE(),
[MATERIAL DESC] IN {"gold","gld"}, "Aspertame",
[MATERIAL DESC] IN {"Green","grn"}, "Stevia",
CONTAINSSTRING([MATERIAL DESC],"Natura"), "Sucralose",
CONTAINSSTRING([MATERIAL DESC],"sugarlite"),
BLANK()
)

And this is the error message I am getting :

[rsbin]

@monojchakrab ,

It seems to not like the last line condition with Blank().  Written this way seems to get you the result you are looking for I believe.  The Blank() below is your "else" condition.

Active with IN = 
SWITCH(TRUE(),
[MATERIAL DESC] IN {"gold","gld"}, "Aspertame",
[MATERIAL DESC] IN {"Green","grn"}, "Stevia",
CONTAINSSTRING([MATERIAL DESC],"Natura"), "Sucralose",
BLANK()
)

MATERIAL DESCActive with IN

gold	Aspertame
gld	Aspertame
Green	Stevia
grn	Stevia
Natura	Sucralose
sugarlite

Hope you can get this to work for you.

[monojchakrab]

Hey @rsbin ,

This piece of code :

Active with IN = 
SWITCH(TRUE(),
[MATERIAL DESC] IN {"GOLD", "GLD"},"Aspertame",
[MATERIAL DESC] IN {"GREEN", "GRN"},"Stevia",
CONTAINSSTRING(mastertable[MATERIAL DESC],"NATURA"), "Sucralose",
CONTAINSSTRING(mastertable[MATERIAL DESC],"SUGARLITE"),"Half-Sugar",
"Not Relevant"
)

does not return any error, but it actually does not check correctly the 1st two conditions with the IN operator :

Would you have a fix for this? I think we are almost there....

Thanks for being with me so far!

appreciate

[rsbin]

@monojchakrab ,

The "IN" Operator is looking for an exact string.  So you would need to enter the entire phrase " SUGAR FREE GOLD PELLETS 300".  This is where I think you might be better off using CONTAINSSTRING.

Active with IN = 
SWITCH(TRUE(),
OR ( CONTAINSSTRING( [MATERIAL DESC], "GOLD"), CONTAINSSTRING( [MATERIAL DESC], "gld" )), "Aspertame",
[MATERIAL DESC] IN {"Green","grn"}, "Stevia",
CONTAINSSTRING([MATERIAL DESC],"Natura"), "Sucralose",
BLANK()
)

MATERIAL DESC                             Active with IN

gold	Aspertame
gld	Aspertame
Green	Stevia
grn	Stevia
Natura	Sucralose
sugarlite
SUGAR FREE GOLD PELLETS 300	Aspertame

It looks to me like you would need to choose a combination of these, depending on how many different variations of [Material Desc] you have.  The advantage of the SWITCH statement in this case, is you can have multiple lines of conditions and it makes it much easier to read instead of your typical nested if Statements.

Good Luck and I'd be interested to know how you best make this work for you.

[monojchakrab]

Thanks @rsbin - Let me find a workaround, but at this stage I am not seeing anything beyond using containsstring with multiple OR condition as there seems to be no other way to work with partial strings