Understanding Power BI CONTAINSSTRING and CONTAINS in DAX

Summary of CONTAINSSTRING and CONTAINS

Use the `CONTAINSSTRING` function when you need to test whether one text value appears inside another text value. On the other hand, use `CONTAINS` when you require a row context comparison against a table or column value in DAX. These functions address a common analytical challenge: analysts often need to flag customers, products, or comments that include specific keywords. They also need to filter records by text patterns without creating a more complex search model.

Problem Definition: Identifying Text Matches

A frequent reporting challenge is identifying rows where text contains a specific word or phrase. For instance:

• Which customer comments mention delivery?
• Which product names include the word “premium”?
• Which accounts have notes that contain “urgent”?
• Which records should be flagged as matching a keyword search?

This need is practical in dashboards and semantic models. Often, a report requires a simple yes or no result before the data can be filtered, categorized, or counted. Without a text-match expression, the model cannot easily classify rows based on a partial string match.

Key Concept: Text Containment Check

The core idea behind these functions is straightforward. A text containment check determines whether one string appears within another string. If the answer is yes, the expression returns true; if no, it returns false. This allows you to build rules such as:

• Does this comment contain the word “delayed”?
• Does this product name contain “Pro”?
• Does this field include a specific code?

The crucial aspect is that the match is based on text content rather than exact equality.

Data Example: Analyzing Text Containment

Consider the following small table:

If we want to find comments that contain the word “delayed,” the expected result is:

If the analysis is based on the Product Name and we search for “Premium,” the result is:

This is the type of flag analysts use to create filtered views, segment counts, and exception reports.

Tool Implementation: Using CONTAINSSTRING and CONTAINS

What is CONTAINSSTRING?

In Power BI DAX, `CONTAINSSTRING` checks whether one text string appears inside another text string.

Syntax:

CONTAINSSTRING(<text>, <substring>)

Example:

CONTAINSSTRING([Comment], "delayed")

This returns a Boolean value:

• True if the text in [Comment] contains “delayed”
• False if it does not

What is CONTAINS?

`CONTAINS` serves a different purpose. It checks whether a table contains a row with matching values in specified columns.

Syntax:

CONTAINS(<table>, <column>, <value>[, <column>, <value> ...])

Example:

CONTAINS(CustomerTable, CustomerTable[CustomerType], "Premium")

This is a table membership check, not a plain text search function. In practice, many text-search use cases are handled with `CONTAINSSTRING`, while `CONTAINS` is used when matching columns inside a table expression.

Applied Example: Creating a Keyword Flag

A common DAX column or measure pattern is to create a keyword flag. For instance:

Keyword Flag:

IF(CONTAINSSTRING([Comment], "urgent"), "Match", "No Match")

Using the sample data:

If the requirement is to find rows where the product name contains “Premium”:

IF(CONTAINSSTRING([Product Name], "Premium"), "Premium Item", "Other")

Result:

This pattern is often used before counting matches or filtering a visual.

Why This Feature Exists: The Need for Text Analysis

Default equality checks are insufficient for text analysis. A standard comparison like:

[Comment] = "delayed"

only matches exact text, which is too limited for many reporting needs. Business users do not typically enter data in a perfectly standardized manner. They may write:

• delayed shipment
• shipment delayed
• delayed by carrier
• delivery delayed

A containment check addresses this issue by looking for a partial match within the full text value. This is why `CONTAINSSTRING` is valuable in semantic models and report logic.

Important Behavioral Details: Understanding Functionality

Case Sensitivity

`CONTAINSSTRING` is not case-sensitive in DAX. A search for "urgent" will also match "Urgent".

Blank Behavior

If the text value is blank, the function returns false. This is significant when your source data has missing notes or empty labels.

Substring Matching

The function checks for a sequence of characters anywhere in the text. A search for "pro" will match:

• Pro
• Premium product
• Approved

While this can be useful, it may also lead to false matches if the keyword is too short.

Exact Match versus Partial Match

If you require a full-word or exact business code match, containment may be too broad. For example, searching for "art" could match "Cart" and "Smart". This is a modeling decision rather than a bug.

CONTAINS versus CONTAINSSTRING

This is a common migration issue. Use `CONTAINSSTRING` for text searches inside a string value. Use `CONTAINS` when you need to test whether a table contains a matching row for specific column values. They address different problems.

Filter Context

When using these functions inside a measure, the result depends on the current filter context. This means the same expression can yield different results based on the rows visible in the report. If you need row-by-row classification, a calculated column is often simpler.

Real Usage Patterns: Practical Applications

Common use cases include:

• Keyword flags in comments or notes
• Product categorization from naming conventions
• Exception detection
• Customer feedback tagging
• Filtering records with known text patterns
• Building counts of rows that mention a topic
• Identifying records with priority terms such as “urgent” or “escalation”

A few practical examples include:

• Flagging support tickets that contain “refund”
• Identifying products whose names contain “Pro”
• Finding orders with comments that contain “delayed”
• Counting accounts with notes that mention “churn”

These are the types of patterns analysts use when the source system does not provide a clean categorical field.

Conclusion: Choosing the Right Function

Use `CONTAINSSTRING` when the business question pertains to text appearing inside text. Use `CONTAINS` when the business question involves row existence in a table expression. If you are building a report filter, a calculated column with a containment flag is often easier to maintain than repeating the text search in every visual.