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:
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:
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?
Hey Rehan,
absolutely, you can combine multiple CONTAINSSTRINGS. For your case that would be:
If both should apply you have to combine them with a “&&” instead of a “||”
Let me know if that works for you.
Best regards
Denis