Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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
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
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 :
Is there a workaround to this, or am I missing something?
Nicely done. In essence allows us to do a "like" within a switch statement.
Just what I needed.
Jonathan
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |