Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Switch function - with a wild card or without exact expression

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.

 

                                                                       

Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    9 years ago

    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

     

4 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    9 years ago

    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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

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

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      v-jiascu-msft,

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

      Just what I needed.

       

      Jonathan 

    • monojchakrab's avatar
      monojchakrab
      Resolver III
      4 years ago

      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?