Table of Contents
The simple formula for “If Contains” in Power BI is an expression that checks if a specified text or value is present within a given column or measure. It follows the format of IF(CONTAINS(table_name, column_name, value), true_result, false_result), where table_name refers to the data table, column_name is the specific column to search within, and value is the text or value to check for. If the specified value is found in the column, the formula returns the true_result, otherwise it returns the false_result. This formula is commonly used for filtering and conditional formatting in Power BI reports.
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 )
Note: You can find the complete documentation for the CONTAINSSTRING function in DAX .
Additional Resources
The following tutorials explain how to perform other common tasks in Power BI: