Switch and Containsstring not replacing all values as required

[monojchakrab]

Hiya All,

I am coming across a strange problem while using the SWITCH function with CONTAINSSTRING and the code is as below :

Size = 

SWITCH(TRUE(),
    CONTAINSSTRING('summary table'[Title],"100")||CONTAINSSTRING('summary table'[Title],"110"),
    "100",
    CONTAINSSTRING('summary table'[Title],"300"),
    "300",
    CONTAINSSTRING('summary table'[Title],"50"),
    "50",
    CONTAINSSTRING('summary table'[Title],"Jar"),
    "80g",
    CONTAINSSTRING('summary table'[Title],"Pouch"),
    "500g",
blank()
)

While it is doing all replacements as per the requirement, its just the lines which have "pouch" in the title, it is just not returning the target value I want in the column[size]. I have tried with all possible unique key word searches in the [Title] for this particular product/value.

Screengrab below :

not returning the target value

Here's the title :

The target is to return the [size] as 500 gm if the [title] contains "Pouch"...

Am I missing something in my code?

Appreciate the help.

Best

[AntonioM · Accepted solution]

Hi, 

The titles with 'pouch' also have '50' in them (because of the 500g), so your SWITCH statement is picking that up first, then putting '50' in the size column. 

SWITCH always goes through each of the statements until it finds a match, so if you put the 'pouch' option first, then you shouldn't have this problem (As long as none of the other titles just so happen to have 'pouch' in).

Alternatively, you could also try changing the 50 to '50g', as this wouldn't pick up the 500.

[AntonioM · Accepted solution]

Hi, 

The titles with 'pouch' also have '50' in them (because of the 500g), so your SWITCH statement is picking that up first, then putting '50' in the size column. 

SWITCH always goes through each of the statements until it finds a match, so if you put the 'pouch' option first, then you shouldn't have this problem (As long as none of the other titles just so happen to have 'pouch' in).

Alternatively, you could also try changing the 50 to '50g', as this wouldn't pick up the 500.

[monojchakrab · Accepted solution]

Thanks @AntonioM ...gotcha!