WhatTheFact_Logo_Transparent_353x76px

Check if a text contains a specific value in DAX

This post is also available in: Deutsch

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:

LinkedIn
Twitter
WhatsApp
Telegram
Email
Print
Denis Selimovic

Denis Selimovic

As a user from the very beginning, Denis Selimovic is passionate about Power BI and everything related to it. In his blog WhatTheFact.bi, he writes about the latest developments in Power BI and provides tips and tricks on the subject. Besides being a Power BI enthusiast, blog author, speaker and principal consultant, he is also co-organizer of the Meetup Power BI User Group Switzerland.

Kommentare:

5 2 votes
Article Rating
Subscribe
Notify of
guest
2 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Rehan
Rehan
1 month ago

Hi Denis

interesting tip for power BI, I have a question in regards to more than one word to be check in a colums. For example, if I want to check in column containing text “Product delivered to customer L4 belongs to Royal Group”.
I would like to use a function that will return the value L4 and Royal Group.

Is there a way to find more than one word in a string?

2
0
Would love your thoughts, please comment.x
()
x
Wait!

Follow me on LinkedIn and you’ll gain access to valuable insights, expert tips, and industry updates related to Power BI. Stay ahead of the curve and supercharge your analytics skills today!

Supercharge your Power BI skills!