Check if a text contains a specific value in DAX

February 26, 2023
Denis Selimovic
DAXcomment 3Created with Sketch Beta.2 comments

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:

Products to be categorized
Products to be categorized

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:

Calculated column with CONTAINSSTRING
Calculated column with CONTAINSSTRING

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:

image 3
New column based if column contains text “bike”

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:

Share:
5 2 votes
Article Rating
Subscribe
Notify of
guest
2 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments