Switch function - with a wild card or without exact expression

[Anonymous]

Hi Community

I am working on an experession, where I am using a switch - True function. 

Basically I have various columns with string values and I am trying to create a new column based upon values in these columns.

I am working something like this

custom column  = SWITCH( TRUE(),

                           OR('MyTable'[Col1] = "Organic Search" , ''MyTable'[Col2] =  "google.com.au") ,"Search",

                                 'MyTable'[Col2] = "facebook.com" , "social",

                                 'MyTable'[Col3] = "%mySTRING%" , "myResults"

)

In this above expression, I want to work for the last line Italic, where I am do not have exact match but only a part of the expression. I can not figure out how to build the expression, as we do regexp or like in SQL.

Any help, hints, direction would be highly appriciated.

regards.

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

Function "Search" could help in your scenario. Please have a try.

custom Column =
SWITCH (
    TRUE (),
    OR ( 'Table3'[Col1] = "Organic Search", 'Table3'[Col2] = "google.com.au" ), "search",
    'Table3'[Col2] = "Facebook.com", "social",
    SEARCH ( "test", 'Table3'[Col3], 1, 0 ) > 0, "myresults"
)

Best Regards!

Dale

[monojchakrab]

Hi @v-jiascu-msft ,

I liked the solution, except in my case the OR has to work on the same column and when I am doing that as per the code below :

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

Its returning blank for the rows where the OR should return true. Please see the screen grab below :

OR function not returning anything

Is there a workaround to this, or am I missing something?

[Anonymous]

@v-jiascu-msft,

Nicely done. In essence allows us to do a "like" within a switch statement. 

Just what I needed.

Jonathan 

[Anonymous]

Thank you @v-jiascu-msft, seems what I was after. Will give it a try.