How to use the Switch function to look for strings

[monojchakrab]

Hey All,

I am looking to use the SWITCH function to search thru' a string and then if the string is found, to return the required value. The code is as follows :

Active = 
SWITCH('summary table'[Title],
    "?calorie control",
"sucralose")

But it is returning nothing.

If I use the SEARCH function within the SWITCH function, it is returning an error, stating the SWITCH function cannot work with a TRUE/FALSE value [sreen grab below]

error in switch function with search

Is there a way to solve this - I know this can be done with an IF & SEARCH combination, but the code is getting too cumbersome.

Any help appreciated.

[AlexisOlson · Accepted solution]

Putting in an OR isn't a problem.

SWITCH (
    TRUE (),
    CONTAINSSTRING ( [Title], "calorie control" ) || CONTAINSSTRING ( [Title], "cook & bake" ) , "Sucralose",
    CONTAINSSTRING ( [Title], "natural" ) , "Stevia",
    CONTAINSSTRING ( [Title], "sugar control" ) , "Aspertame"
)

But this isn't really any different than without the OR.

SWITCH (
    TRUE (),
    CONTAINSSTRING ( [Title], "calorie control" ), "Sucralose",
    CONTAINSSTRING ( [Title], "cook & bake" ) , "Sucralose",
    CONTAINSSTRING ( [Title], "natural" ) , "Stevia",
    CONTAINSSTRING ( [Title], "sugar control" ) , "Aspertame"
)

[Ashish_Mathur · Accepted solution]

Hi,

This calculated column formula works

=LOOKUPVALUE(search_terms[Result],search_terms[Search string],FIRSTNONBLANK(FILTER(VALUES(search_terms[Search string]),SEARCH(search_terms[Search string],Data[Title],1,0)),1))

The search terms table looks like this

Search stringResult

Calorie control	Sucralose
Cook & bake	Sucralose
natural	Stevia
sugar control	Aspertame

Hope this helps.

[monojchakrab]

Hey @Ashish_Mathur ,

I am trying to search thru the column called Title and whereever it matches - something like OR("calorie control","cook & bake"), it replaces that with sucralose, e.g. and so on.

Some workaround with the SWITCH function is giving me a limited solution but I am not able to find a way to use multiple strings within the search function like using a || or , the OR function, like one would do in Excel.

The code I am using now, with limited result is as follows :

Active = 
SWITCH(TRUE(),
    SEARCH("calorie control",'summary table'[Title],,0)>1,
"Sucralose",
    SEARCH("natural",'summary table'[Title],,0)>1,
"Stevia",
    SEARCH("sugar control", 'summary table'[Title],,0)>1,
"Aspertame",
    search("cook & bake",'summary table'[Title],,0)>1,
"Sucralose",
blank())

Hope that explains what I am trying to solve for here.

Aprpeciate the time and the help.

[Ashish_Mathur]

Hi,

Share some data to work with.  Also, share an exhaustive 2 column dataset where the first column contains the search strings such as Calorie Control, natural and the second column which contains the result you would like to see such as Sucralose, Stevia.

Furthermore, what result do you expect to see in a spare column if you have Calorie Control and Natural in the same cell?

[monojchakrab]

Title	MPU	Active
Equal Sweetener, Sugar Free, Low Calories, Sugar Control, 50 Sachets, Pack of 5	5	Aspertame
Equal Stevia Natural Sweetener, Sugar Free, 100 Sachets - Pack of 1	1	Stevia
Equal Sweetener, Sugar Free, Low Calories, Sugar Control, 50 Sachets, Pack of 3	3	Aspertame
Equal Sweetener, Sugar Free, Low Calories, Sugar Control, 100 Sachets, Pack of 4	4	Aspertame
Equal Sweetener, Sugar Free, Low Calories, Sugar Control, 100 Sachets, Pack of 2	2	Aspertame
Equal Sweetener, Sugar Free, Zero Calorie, Cook & Bake, 80g Powder Jar, Pack of 1	1
Equal Sweetener, Sugar Free, Low Calories, Sugar Control, 300 Tablets, Pack of 2	2	Aspertame
Equal Stevia Natural Sweetener, 100 Tablets - Pack of 2	2	Stevia
Equal Sweetener, Sugar Free, Zero Calorie, Calorie Control, 100 Sachets, Pack of 6	6
Equal Sweetener, Sugar Free, Low Calories, Sugar Control, 100 Sachets, Pack of 6	6	Aspertame
Equal Stevia Natural Sweetener, Sugar Free, 50 Sachets - Pack of 2	2	Stevia
Equal Sweetener, Sugar Free, Zero Calorie, Calorie Control, 300 Tablets, Pack of 2	2
Equal Sweetener, Sugar Free, Low Calories, Sugar Control, 300 Tablets, Pack of 3	3	Aspertame
Equal Stevia Natural Sweetener, Sugar Free, 100 Sachets - Pack of 2	2	Stevia
Equal Sweetener, Sugar Free, Low Calories, Sugar Control, 100 Sachets, Pack of 3	3	Aspertame
Equal Stevia Natural Sweetener, 100 Tablets - Pack of 4	4	Stevia
Equal Sweetener, Sugar Free, Zero Calorie, Calorie Control, 100 Sachets, Pack of 2	2
Equal Sweetener, Sugar Free, Low Calories, Sugar Control, 100 Tablets + 10 Tablets Free Tablets, Pack of 3	3	Aspertame
Equal Sweetener, Sugar Free, Zero Calorie, Calorie Control, 100 Tablets, Pack of 6	6
Equal Sweetener, Sugar Free, Zero Calorie, Calorie Control, 100 Tablets, Pack of 3	3

The column called [active] is the target column. The column to search thru' is the 1st column called [Title].

Does this work for you?

To answer the question you have asked at the end - that is precisely the problem I am trying to solve for. Which means there has to be a way to use a kind of OR function, where the search can happen across multiple strings at the same time.

E.g. If [Title] contains "Calorie control" OR "Cook & bake", Then return "Sucralose"

Hope I have been able to explain a little more clearly for you.

Appreciate your time and help

[Ashish_Mathur · Accepted solution]

Hi,

This calculated column formula works

=LOOKUPVALUE(search_terms[Result],search_terms[Search string],FIRSTNONBLANK(FILTER(VALUES(search_terms[Search string]),SEARCH(search_terms[Search string],Data[Title],1,0)),1))

The search terms table looks like this

Search stringResult

Calorie control	Sucralose
Cook & bake	Sucralose
natural	Stevia
sugar control	Aspertame

Hope this helps.

[monojchakrab]

Very interesting solution @Ashish_Mathur .

Will check this out

[Ashish_Mathur]

Thank you.

[AlexisOlson · Accepted solution]

Putting in an OR isn't a problem.

SWITCH (
    TRUE (),
    CONTAINSSTRING ( [Title], "calorie control" ) || CONTAINSSTRING ( [Title], "cook & bake" ) , "Sucralose",
    CONTAINSSTRING ( [Title], "natural" ) , "Stevia",
    CONTAINSSTRING ( [Title], "sugar control" ) , "Aspertame"
)

But this isn't really any different than without the OR.

SWITCH (
    TRUE (),
    CONTAINSSTRING ( [Title], "calorie control" ), "Sucralose",
    CONTAINSSTRING ( [Title], "cook & bake" ) , "Sucralose",
    CONTAINSSTRING ( [Title], "natural" ) , "Stevia",
    CONTAINSSTRING ( [Title], "sugar control" ) , "Aspertame"
)

[monojchakrab]

This is a very elegant solution @AlexisOlson .

I was wondering what we need the TRUE() for? I have seen this used with the SWITCH function in a number of other instances, but never really understood why.

Would you be able to throw some light on that?

[AlexisOlson · Accepted solution]

The SWITCH function returns the first result that matches the first argument (in this case, TRUE). So, in this pattern, it returns the result following the first test that evaluates as being true.

More detail here:
https://p3adaptive.com/2015/03/the-diabolical-genius-of-switch-true/

[Ashish_Mathur]

Hi,

Instead of sharing the formula, explain the question in simple language and show the expected result.