Forum Discussion

monojchakrab's avatar
monojchakrab
Resolver III
4 years ago

Replacing OR condition with IN operator and ContainsString

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.,

Need Help

7 Replies

  • rsbin's avatar
    rsbin
    Community Champion
    4 years ago

    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's avatar
      monojchakrab
      Resolver III
      4 years ago

      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's avatar
        rsbin
        Community Champion
        4 years ago

        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.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    4 years ago

    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.