Check if a text contains a specific value in DAX
This post is also available in:
German
Did you ever need to check if a certain text contains a specific value? While this may seem like a small topic, it’s one that I have come across multiple times in my own work, and I’m sure many others have as well.
In this short article, we’ll explore how to handle this situation using DAX. With just a few lines of code, you’ll be able to easily identify whether your text contains the desired value or not.
Example for our article
Maybe I’ve worked too much in the past with AdventureWorks, but let’s imagine we are selling bikes and we have a product table. Our small example looks like this:
For this column we want to check now, if it contains the text “bike” or if it doesn’t.
The only function you need is CONTAINSSTRING
In this case, we could do an easy approach and add a calculated column to categorize into “bikes” and “accessory”. For that, we only want to check if the value contains the word “bike”.
The solution is quite easy, as the function CONTAINSSTRING is checking if a certain text contains a specific string. For our case, the following calculated column will return TRUE if the text “bike” is found and FALSE when it is not found:
Contains Bike = CONTAINSSTRING( Products[Productname], "bike" )And the result shows now which element contains the word “Bike” and which one doesn’t:
Combination with an IF statement returns the desired result
If you combine that now with an IF or a SWITCH statement, you can get the categorization that we wanted in the first place as a calculated column.
Category =
IF(
CONTAINSSTRING( Products[Productname], "bike" ),
"Bike",
"Accessory"
)The new column contains now our categorization:
Case sensitivity with CONTAINSROW
The only question left now, is the function CONTAINSROW case-sensitive or not? The answer is quite simple, it is not. So it doesn’t matter if you write “bike” or “Bike” or “bIkE” in the function, it will return the same value.
Of course this approach can also be used within a DAX measure, but I had the feeling it’s easier to demonstrate with a calculated column.
Download Demo File
The demo file can be downloaded here:
