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

     

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?