How to Check if a String Contains a Substring in Power BI: Using DAX

How to Check if a String Contains a Substring in Power BI, you can easily check if a string contains a particular substring and return a corresponding value using DAX (Data Analysis Expressions).

This can be incredibly useful for categorizing data or filtering specific values in your reports.

In this guide, we will explain how to create a new column that checks for a substring and returns either “Yes”/”No” or numeric values.

Syntax for Checking a Substring with DAX

To determine whether a string in a specified column includes a certain substring, use the following DAX syntax:

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

Explanation

• New Column: This formula creates a new column called contains_ets.
• Functionality: It checks if the string in the Team column includes the substring “ets”. If it does, the column returns “Yes”; if not, it returns “No”.

Practical Example: Using the “If Contains” Syntax in Power BI

Suppose you have a Power BI table named my_data that contains information about basketball players and their respective teams, as shown below:

Player Name	Team
John Doe	Rockets
Jane Smith	Pistons
Emily Davis	Nets
Mike Johnson	Celtics
Chris Brown	Warriors

Steps to Add a New Column

To check if the Team names contain the substring “ets” and add a new column:

1. Open Power BI: Launch your Power BI desktop application and select your dataset.
2. Click on New Column: In the ribbon, click the New column icon.
3. Enter the Formula: In the formula bar, type the DAX formula:

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

4. Results: After entering the formula, a new column named contains_ets will be generated. This column will contain “Yes” or “No” based on the presence of “ets” in the Team column.

Resulting Table

After applying the above formula, your my_data table will look like this:

Player Name	Team	contains_ets
John Doe	Rockets	No
Jane Smith	Pistons	No
Emily Davis	Nets	Yes
Mike Johnson	Celtics	Yes
Chris Brown	Warriors	No

Using Numeric Values Instead of “Yes” or “No”

If you prefer to return numeric values instead of text, you can modify the formula as follows:

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

In this case, the new column will return 1 if the Team name contains “ets,” or 0 if it does not.

Updated Resulting Table

Applying the modified formula will result in:

Player Name	Team	contains_ets
John Doe	Rockets	0
Jane Smith	Pistons	0
Emily Davis	Nets	1
Mike Johnson	Celtics	1
Chris Brown	Warriors	0

Conclusion

Using DAX to check if a string contains a particular substring in Power BI is a straightforward yet powerful technique.

By creating a new column that reflects this condition, you can categorize your data effectively, whether using “Yes”/”No” or numeric values.

Implementing these methods will enhance your data analysis capabilities in Power BI, allowing for better insights and reporting.

For more information on DAX functions, feel free to consult the official Microsoft documentation.

Remember to replace placeholder links for images and resources with actual relevant resources to provide additional visual support for your readers!