Table of Contents
A “If Contains” formula in Power BI is a simple and efficient way to search for a specific text or value within a larger dataset. This formula allows users to specify a condition or criteria, and if the selected data contains that condition, it will return a desired result. This feature is particularly useful for filtering and organizing data based on specific keywords or patterns. By using the “If Contains” formula, users can easily extract and analyze relevant information from their datasets in a quick and straightforward manner.
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 )
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: