cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Super User II
Super User II

How to use Like function of SQL in Dax

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!

Don't forget to give thumbs up and accept this as a solution if it helped you!!!
You can follow me on LinkedIn

Please take a quick glance at newly created report:
My Report
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User I
Super User I

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

HOME1
USA2

 

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 

search.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

1 REPLY 1
Highlighted
Super User I
Super User I

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

HOME1
USA2

 

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 

search.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors