Power BI: A Simple Formula for “If Contains”


You can use the following syntax in DAX to check if a string contains a particular substring and return either “Yes” or “No” as a result:

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

This particular syntax creates a new column named contains_ets that returns “Yes” if the string in the Team column contains the substring “ets” or “No” if it does not.

The following example shows how to use this syntax in practice.

Example: How to Use “If Contains” in Power BI

Suppose we have the following table in Power BI named my_data that contains information about basketball players on various teams:

Suppose we would like to add a new column that returns either “Yes” or “No” to indicate if the corresponding string in the Team column contains “ets” or not.

To do so, click the New column icon:

Then type in the following formula into the formula bar:

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

This will create a new column named contains_ets that contains the value “Yes” or “No” based on the corresponding string  in the Team column:

The contains_ets column contains either “Yes” or “No” to indicate if the corresponding string in the Team column contains “ets” anywhere in the string.

If you’d like, you could instead return numeric values such as 1 or 0 instead of “Yes” or “No” by using the following syntax:

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

This will create a new column named contains_ets that contains the value 1 if the corresponding string in the Team name contains “ets” or 0 if it does not:

Note: You can find the complete documentation for the CONTAINSSTRING function in DAX here.

Additional Resources

The following tutorials explain how to perform other common tasks in Power BI:

How to Count Number of Occurrences in Power BI
How to Write an IF Statement in Power BI
How to Write an IF Statement with Multiple Conditions in Power BI

Leave a Reply

Your email address will not be published. Required fields are marked *