How to use Like function of SQL in Dax

[Tahreem24]

Hi,

My column name say Name contains long text string out of which i want to extract only specfic word so how can I achieve this requirement?

I tried using Search/Find/Contains functions but didn't get the expected result.

Input Column value:                                                                          Output Column

Experiment_RSA_home_loans_Brand_BMM                                     Home

Experiment_RSA_Home_Loan_Brand_Exact                                      Home

Exp - Core - Phrase - USA - New Ads                                               USA

Any lead would be really appreciated!

[AnthonyTilley · Accepted solution]

hi @Tahreem24 

if your looking for a way to check a string against a list of values and return one of those valuse if it exsists in your string then the method below should work 

Create a table of values in this case mine is called search_Values

the table should have two colunms the first is the search and the second a numeric id for each value (must be unique)

example below 

SearchID

HOME	1
USA	2

then use this colunm to get do your search aginst the values in the table 

Column = 
-- Declare a veriable to hold the id number of search term
VAR incl =  
        -- MAXX will check each row of the search values table to see if the value exsists in the input colunm and return the max id 
        MAXX(Search_Values,
                if(
                    --use find to check if the value in the search values, search colunm is in the string
                    FIND(
                    UPPER(Search_Values[Search]),
                    UPPER('Table'[Input Column]),1,0)
                    -- if the value is in the string then return the id else return blank
                > 0 ,Search_Values[ID],blank()))
-- if several values exist then only the max id will be returned as you can only have one valuein the colunm

--use the id value returned above to look up the word and return it  
 var ret = LOOKUPVALUE(Search_Values[Search],Search_Values[ID],incl)
 -- return search term
 RETURN ret    

Warning - this can only be used to return a single value. if your string had both "HOME" and "USA" then it would only return USA as this is the highest id value