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 = FIND("A",DimCustomer[FullName],,-1)
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.
Search = SEARCH("A",DimCustomer[FullName],,-1)
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
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
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.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.