Find a Text Term in a Field in Power BI Using DAX Functions

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

In Power BI, there are multiple ways of searching for a text term inside a text field, you can use Power Query for doing this operation or calculations in DAX. In this post, I’ll explain some functions in DAX that you can use to do this calculation. Most of these functions can be used inside a measure for dynamic calculation. In this post, you will learn about a few of DAX functions that deal with search a text term in a text field.

Sample Data

I am using the DimCustomer table from the AdventureWorks excel file, and only two columns of that which are CustomerKey and FullName;

FIND

Find is a DAX function that searches for a term inside a text field, and returns the starting position of that item (position index starts from one). The way that you can use this function is like below:

FIND(<text term to search for>,<the column you are searching into>,[<starting index for search>],[<result if the text term is not found>])
Here is an example of using this function:

The above expression, searches for the term “A” inside the column FullName of DimCustomer table, starting from the very first of the value in that column’s cell and if it can’t find the value, it returns -1.

The expression above is defined as a column, so as a result, it will run for every row (however, you can use the FIND function in a measure if you want). If it cannot find the value it returns -1, and if it can find it, it returns the index of that in the text (it returns the first index of that term if it appears multiple times). For example, The customer full name “Janet Alvarez” contains the character “A” as the seventh character in the text, so the return is 7. However, “Ruben Torres” doesn’t contain “A”, and it returns -1.

FIND Is Case Sensitive

You might have wondered, why the result of the above expression for “Jon Yang” is still -1, although that we have character “a” in there. the reason is that FIND is a case sensitive function. There is a difference between the above expression if you use “A” or “a” in the FIND;

Another thing is that although the last parameter of the FIND is an optional parameter if you don’t pass a value to it, it returns an ERROR.

SEARCH

Search is very similar to FIND, the only difference is that Search is NOT case sensitive. There is no difference between “A” or “a” when you use the Search function.

The example below is using Search function in a calculated column;

Tailoring FIND to not be Cast Sensitive

You can easily change FIND or SEARCH to return exactly the same result too. meaning that you can make FIND not case sensitive, or SEARCH case sensitive with the help of other function. here is an example of how you can make FIND not case sensitive:

The above expression is using UPPER to make the FullName’s value all uppercase, and then compare it with “A”, or you can do lowercase, and then compare it with “a”.

ContainsString

FIND and SEARCH functions are returning the starting index of the search term. However, the ContainsString function returns a boolean result that is that term found in the text or not. the result of this function is true or false. ContainsString just need to parameters;

ContainsString(<the column you are searching into>,<text term to search for>)

Here is the result of this function used in an example:

ContainsString is not case sensitive, and it returns true for any of those values that the Search function returns a value not equal to -1 in our example.

ContainsStringExact

There is a case sensitive version of the ContainsString, called ContainsStringExact. The function can be used similar to the previous one;

Exact

Exact is not a function to search through a text. This is a function to check the equality of value with a text, the two texts should be exactly the same. This function is case sensitive. Exact, gets two text values and check if they are exactly the same or not, the result is a true or false value;

Exact(<text 1>,<text 2>)

Using the Exact, you can write an expression like below;

Other Functions

There are other functions that work with text search such as Contains, which needs its own blog post to explain. Note that the term lookup can be also done using Power Query, and if the purpose is to do pre-calculation, then it is better to do that in Power Query as a transformation. However, if the scenario needs to be dynamic, then using functions above in a measure helps. if you are not sure, when you should use Power Query transformation, and when you should use DAX, read my article here.

All in One

Here is a summary of these functions;

Video

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.

Leave a Reply

Your email address will not be published. Required fields are marked *