How to Use CONTAINS in Power BI

Chances are you're trying to figure out if one piece of text exists inside another in Power BI, and the DAX function name CONTAINS seems like the obvious choice. It's a logical assumption, but it’s also one of the most common pitfalls for new and experienced Power BI users alike. The CONTAINS function doesn't actually search for substrings inside your text fields.

This tutorial will clear up that confusion for good. We'll show you what the CONTAINS function really does, which functions you should actually use for searching text (like CONTAINSSTRING), and provide practical, step-by-step examples for both scenarios.

Understanding the Real Job of the CONTAINS Function

First, let's set the record straight: the DAX CONTAINS function is designed to check for the existence of a full value within a column. Think of it as answering the question, "Is this specific value present anywhere in this list?" It returns a single TRUE or FALSE for the entire check.

CONTAINS Syntax

The basic syntax looks like this:

CONTAINS(<,table>, <,columnName>, <value>)

• <,table>,: The table you want to check.
• <,columnName>,: The column within that table where you're looking for the value.
• <,value>,: The exact value you are searching for.

Imagine you have a Products table with a [Color] column. The formula CONTAINS(Products, [Color], "Blue") will return TRUE only if a row exists where the [Color] value is exactly "Blue". It will return FALSE for rows containing "Light Blue" or "Dark Blue".

This function isn’t checking for parts of text, it's checking for entire cell values.

So, When is CONTAINS Actually Useful?

The CONTAINS function shines when you use it as a lookup check inside other functions, like IF or CALCULATE. It’s perfect for determining if an item from one list exists in another reference list.

Example Scenario: Let’s say you have a primary Sales table and a separate PriorityAccounts table that just lists the account IDs of your VIP customers. You want to create a measure that tells you if a sale belongs to a priority account.

Here’s how you could use CONTAINS in a measure:

Is Priority Sale = IF( CONTAINS( PriorityAccounts, PriorityAccounts[AccountID], SELECTEDVALUE(Sales[AccountID]) ), "Yes, Priority", "No, Standard" )

Let's break that down:

• SELECTEDVALUE(Sales[AccountID]): This grabs the Account ID for the current context (e.g., the current row in a table visual).
• CONTAINS(...): This part then checks if that specific Account ID from the Sales table exists anywhere in the [AccountID] column of your PriorityAccounts table.
• IF(...): If CONTAINS returns TRUE (meaning the ID was found), the measure returns "Yes, Priority". Otherwise, it returns "No, Standard".

In this use case, CONTAINS acts as a powerful and efficient lookup tool. But for searching within text? We need a different tool for the job.

How to Actually Find Text Within a String

You came here to find "Premium" inside your product name "Super Premium Widget." For this common task, Power BI gives us a much better function: CONTAINSSTRING.

The name says it all. This function is designed to do exactly what you thought CONTAINS did. It checks for a text substring within another text string on a row-by-row basis.

CONTAINSSTRING Syntax

It's much simpler and more intuitive for this kind of work:

CONTAINSSTRING(<,within_text>, <,find_text>)

• <,within_text>,: The text or column where you are searching (e.g., Products[Product Name]).
• <,find_text>,: The substring you want to find (e.g., "Premium").

This function is not case-sensitive and returns a simple TRUE or FALSE, making it perfect for creating calculated columns and measures.

Practical Example 1: Creating a Calculated Column Using CONTAINSSTRING

Let's say you're a marketer with a table of campaign data. Your campaign names are a bit messy, but they all contain a keyword identifying the channel, like 'Facebook', 'Google', or 'Email'. You want to create a new column to categorize these campaigns cleanly.

Here's a sample of the Campaigns table:

• Q4 Holiday Sale - Facebook
• Summer Promo Google Ads
• Welcome Series Email Blast
• Retargeting campaign (Facebook)

Step-by-Step Instructions:

1. In Power BI Desktop, navigate to the Data view by clicking the table icon on the left-hand pane.
2. Select the table you want to modify (in our case, Campaigns).
3. From the toolbar at the top, click on New Column.
4. In the formula bar that appears, enter the following DAX formula. We'll use a nested IF to check for multiple keywords:

Campaign Channel = IF( CONTAINSSTRING(Campaigns[Campaign Name], "Facebook"), "Social Media", IF( CONTAINSSTRING(Campaigns[Campaign Name], "Google"), "Paid Search", IF( CONTAINSSTRING(Campaigns[Campaign Name], "Email"), "Email", "Other" ) ) )

1. Press Enter to confirm.

Instantly, Power BI evaluates each row. For "Q4 Holiday Sale - Facebook", CONTAINSSTRING finds "Facebook" and returns TRUE, so the formula assigns "Social Media" to the [Campaign Channel] column. For "Summer Promo Google Ads", it finds "Google" and assigns "Paid Search", and so on.

Practical Example 2: Filtering a Measure Using CONTAINSSTRING

Calculated columns are great, but measures offer more dynamic power in your reports. Let's create a measure to calculate total revenue, but only for products with "Premium" in their name. This allows a user to slice and dice by date, region, or any other dimension, and the calculation will adapt.

Here, we'll combine CONTAINSSTRING with the two most important DAX functions: CALCULATE and FILTER.

Step-by-Step Instructions:

1. In Power BI, go to the Report view.
2. In the Home ribbon, click New Measure.
3. In the formula bar, type the following DAX expression:

Premium Sales = CALCULATE( SUM(Sales[Revenue]), FILTER( Products, CONTAINSSTRING(Products[Product Name], "Premium") ) )

How This Formula Works:

• CONTAINSSTRING(Products[Product Name], "Premium"): This checks each product name for the word "Premium" and returns TRUE or FALSE.
• FILTER(Products, ...): FILTER iterates through every row of the Products table and creates a temporary, virtual table containing only the rows where the CONTAINSSTRING check was TRUE.
• CALCULATE(SUM(Sales[Revenue]), ...): CALCULATE modifies the filter context. It calculates the sum of revenue, but to only consider the products in the filtered table.

You can now add this "Premium Sales" measure to a card visual or a chart to see your premium product performance at a glance.

What About the SEARCH and FIND Functions?

While CONTAINSSTRING is typically the best choice, it's good to know about two other related functions: SEARCH and FIND.

• SEARCH: Finds a text substring and returns its starting position (a number). It is not case-sensitive.
• FIND: Finds a text substring and returns its starting position. It is case-sensitive.

The key differences are case sensitivity and their return value. Since they return a number or an error (if the text isn't found), they aren't a direct TRUE/FALSE check. To use them for filtering, you often need to wrap them in an ISERROR function.

For example: IF(ISERROR(SEARCH("premium", [Product Name])), "Standard", "Premium"). This works, but it's more complex. For a simple "is it in there?" check, CONTAINSSTRING is cleaner and more direct.

Final Thoughts

Mastering DAX requires understanding these kinds of specific details. The key takeaway is simple: use CONTAINS when you need to see if a table contains a complete, exact row value. When you need to check if a piece of text is located inside another text string, CONTAINSSTRING is the clear winner for its simplicity and directness.

Learning these nuances is powerful but undeniably time-consuming, especially when all you need is a quick answer. At Graphed, we created a solution that skips the steep learning curve entirely. Instead of wrestling with DAX functions, you can just connect your data and ask in plain English: "Show me my sales revenue just for campaigns containing 'Facebook'." Our platform translates your request into the necessary code, instantly building live, interactive dashboards that answer your specific questions, allowing you to focus on strategy instead of syntax.