How to Check for Substrings in Power BI Using DAX

How to Check for Substrings in Power BI Using DAX, Power BI is a powerful tool for data analytics and visualization, allowing users to manipulate and display data in various insightful ways.

One common task that analysts encounter is the need to determine whether a specific substring exists within a column of text data.

In this article, we’ll explore how to accomplish this using DAX (Data Analysis Expressions) and the CONTAINSSTRING function.

We’ll walk through a practical example, complete with tables to illustrate the process.

Understanding CONTAINSSTRING Function in DAX

The CONTAINSSTRING function in DAX checks if a specified substring exists within a given string.

The function returns TRUE if the substring is found and FALSE otherwise. This is particularly useful when analyzing categorical data or checking conditions in text fields.

Syntax Overview

The basic syntax of the CONTAINSSTRING function is as follows:

CONTAINSSTRING(<within_text>, <find_text>)

• within_text: The text string to be searched.
• find_text: The substring you are looking for.

Step-by-Step Example: Checking for “ets” in Team Names

Let’s consider a practical scenario where you have a table named my_data containing information about basketball players and their respective teams.

We want to create a new column indicating whether the team name contains the substring “ets”.

Sample Table: my_data

Player Name	Team
John Doe	Boston Celtics
Jane Smith	Los Angeles Lakers
Mike Johnson	Philadelphia 76ers
Sarah Davis	Miami Heat

Creating a New Column in Power BI

To create a new column that determines if the team name contains “ets”, follow these steps:

1. Open your Power BI Desktop application.
2. Make sure you have the my_data table loaded.
3. Click on the “Modeling” tab in the ribbon.
4. Click on the “New column” icon.

Now, enter the following DAX formula in the formula bar:

contains_ets = 
IF(
   CONTAINSSTRING('my_data'[Team], "ets"),
   "Yes",
   "No"
)

Resulting Table After Adding the “contains_ets” Column

Player Name	Team	contains_ets
John Doe	Boston Celtics	Yes
Jane Smith	Los Angeles Lakers	No
Mike Johnson	Philadelphia 76ers	No
Sarah Davis	Miami Heat	No

Explanation of the Formula

• The IF function checks whether the string “ets” is contained in the Team column.
• If it is found, the new column contains_ets returns “Yes”; otherwise, it returns “No”.

Alternative: Returning Numeric Values

If you prefer to return numeric values instead of text (e.g., 1 for “Yes” and 0 for “No”), you can modify the formula like this:

contains_ets = 
IF(
   CONTAINSSTRING('my_data'[Team], "ets"),
   1,
   0
)

Resulting Table with Numeric Values

Player Name	Team	contains_ets
John Doe	Boston Celtics	1
Jane Smith	Los Angeles Lakers	0
Mike Johnson	Philadelphia 76ers	0
Sarah Davis	Miami Heat	0

Conclusion

By using the CONTAINSSTRING function in DAX, you can efficiently check for substrings within text strings in Power BI.

This functionality is particularly handy for data analysis, allowing analysts to categorize or flag data based on textual content.

With the ability to customize the output (either as “Yes”/”No” or numerical values), you can tailor your analysis to fit your reporting needs.

Now you’re equipped to incorporate substring checks in your Power BI reports, enhancing your data visualization and analytical capabilities.

For more detailed information on the CONTAINSSTRING function and its applications, refer to the official Power BI documentation.

Best Books to learn Tensorflow

Best Books For Deep Learning

5 Free Books to Learn Statistics For Data Science

Best Data Science Books For Beginners