What is Tableau CONTAINS function?
Tableau Contains function is one of the string functions that allows you to manipulate any string data, such as texts, which can be further used in any Tableau calculations. For example, if you have a dataset of customers and you want to identify how many customers have a first name like John, then you can use the contains function to figure out such customers. Tableau contains functions that provide a Boolean output, i.e., True/False, where you can search for a specific substring within a specific text or a text data field.
A sample Tableau contain function is provided below. The below expression is evaluated as True in Tableau as the string United States has the substring United.
Table of contents
Key Takeaways
- Tableau contains one of the string functions that allow you to manipulate any string data, such as texts, which can be further used in any Tableau calculations.
- The CONTAINS function in Tableau returns a Boolean value. If the text field it contains a substring, then it returns True; otherwise, False is returned.
- Tableau contains function is case-sensitive and, by default, assumes a wild card character % added to the start and end of the searched substring.
- You can use the contains function in calculated fields to perform calculations or reporting in Tableau.
Syntax
The syntax of Tableau contains function is provided below:
CONTAINS(string, substring)
The contains function in Tableau returns True if the given string contains the specified substring. Otherwise, it will return a False.
For example, CONTAINS (“Miscellaneous”, “s”) will return True as the word Miscellaneous contains the letter s. Similarly, if you have a string data field say Customer Name with multiple values (as shown below)
Customer Name |
Ryan Taylor |
John Chan |
Johney Nathan |
Joe Ward |
then the formula will return the below result
CONTAINS([Customer Name], “John”)
Customer Name | Output |
---|---|
Ryan Taylor | False |
John Chan | True |
Johney Nathan | True |
Joe Ward | False |
How to use the CONTAINS function in Tableau?
To use the CONTAINS function in Tableau, follow the steps outlined below:
Step 1: Open Tableau Desktop in your system. Connect to your data source in Tableau by using the File Navigator.
Step 2: Click on a New Worksheet or Sheet 1 to go to Worksheet.
Step 3: In the Worksheet, click on Analysis – Create Calculated Field option.
Tableau will open a calculation editor in the view.
Step 4: In the calculation editor screen, specify the name of the field and the logic to calculate the value.
Here, we have created a new calculated field as ContainsDemo and added a Tableau contains formula to identify if the subcategory contains the keyword Table using the CONTAINS function.
Click on OK to save the changes. Once completed, you will be able to view the calculated field in the Data pane.
Step 5: Drag the Revenue to the Columns shelf and Sub-category and newly created field ContainsDemo to the Rows shelf to create a visualization. As you can see, the ContainsDemo field has values as True and False depending on whether the sub-category field contains the keyword Table or not.
Customize the view by dragging the calculated field to the Color and Revenue to the Label in the Marks pane.
Now your Tableau visualization is ready to be viewed.
Note: To assess whether Tableau contains exact matches for strings, consider using other string functions such as LTRIM, RTRIM, UPPER, or LOWER, where you can use an equal operator to identify the matching records.
If a text field in Tableau contains multiple values, then you can’t use the Contains function to perform a substring search. However, for such scenarios, you may create multiple calculated fields to perform such a search.
Let’s now look at some of the examples showcasing the usage of the contains function within Tableau.
Examples
In this section, we will demonstrate examples of using the CONTAINS function in Tableau for different scenarios.
Example #1 – Using with wildcard characters
In this example, we will demonstrate using the CONTAINS function with wildcard characters using the Airline Review dataset. The Airline Review dataset contains details such as the type of traveler, seat type, routes, data flown, reviews, ratings, and other details that can be used to identify the insights from reviews and ratings.
To demonstrate using the CONTAINS with wildcard characters, follow the steps outlined below:
Step 1: Import the Airline Review dataset into the Tableau interface using the File – Open. The imported data can be visible in the Data Source tab in Tableau.
Step 2: Once the data import is successful, navigate to a new worksheet. Right-click on the Data pane and select Create Calculated Field… from the context menu.
Step 3: In the calculation editor screen, provide the name of the field.
In this example, we have named the calculated field as Leisure Travellers.
Define the logic for the calculated field in Tableau.
Here, we have used the CONTAINS function to identify the Leisure Travellers by looking up the keyword Leis in the text field Type of Traveller.
Step 4: Once the Tableau contains formula is defined, click on OK to save the changes.
Tableau will create a calculated field, Leisure Travellers, visible in the Data pane.
Step 5: In the Tableau view, Drag and drop the Type of Travelers, Leisure Travellers to the Rows shelf, and CNT(Route) to the Columns shelf.
Tableau will create a data visualization in the view.
Step 6: Sort the dataset in the Tableau view by clicking on the sort descending icon.
Step 7: Drag the Leisure Travellers field to the Color and control-click the CNT(Route) field from the Columns shelf to the Label in the Marks pane.
Note: Tableau Contains function assumes the wildcard character inherently to the substring. So you don’t have to specify any other wildcard character for the substring.
Example #2 – Nesting CONTAINS functions within each other
In this example, we will demonstrate using the Nesting CONTAINS function within each other using the Global Video Game Sales dataset. The Global Video Game Sales dataset contains sales details for different video games, including information such as ranking, game name, platform, year, genre, publisher, and sales across the EU, Japan, North America, and globally.
To demonstrate using the Nesting CONTAINS function within each, follow the step-by-step guide below:
Step 1: Import the Global Video Game Sales dataset into the Tableau interface using the File Navigator.
Step 2: Click on Analysis. Choose the Create Calculated Field option from the context menu.
Step 3: In the calculation editor, specify the field name. Provide the logic for the field calculation.
Here, we have created a field of Wii games to identify all the video games that have the game name containing Wi or the platform name containing Wi as Wi Game.
Click on OK once the changes are completed to save them. Tableau will automatically create the calculated field visible in the Data pane.
Step 4: Drag and drop the fields to the Tableau view. Here, we have dragged Global Sales to the Columns shelf and Wi Games and Platform to the Rows shelf to create a visualization.
Example #3
In this example, we will demonstrate using the CONTAINS function to validate the email addresses of Amazon Prime Users in Tableau, which contains information about 2500 fictional users of the Amazon Prime subscription service that includes details such as the user’s name, email address, location, subscription plan, payment information, and engagement metrics. Additionally, demographic data such as gender and date of birth are provided, along with user preferences such as favorite genres and devices used to access the platform.
To use the CONTAINS function to validate the email address in Tableau, follow the instructions below.
Step 1: In the Tableau desktop, navigate to the Amazon Prime Users dataset and import it into the Tableau interface.
Step 2: Navigate to Analysis – Create Calculated Field. Create a calculated field in the Data pane.
Step 3: In the calculator editor screen, provide a meaningful name for the field.
Specify the logic to validate the email address of the prime users.
We have used conditional logic to check if the email address contains .org, .NET, or .com to tag as a valid format.
Step 4: Once completed, drag the newly created field, i.e., Email Validation, to the Rows shelf.
As you can see, while the email addresses containing .org and .com are deemed as valid formats, the email addresses containing .net are tagged as invalid formats.
It is because the contains function is case-sensitive, and hence, they have been invalidated by this function.
Step 5: To amend the logic to validate the email address, right-click on the Email Validation field in the Data pane and choose Edit.
Step 6: Amend the logic to add another string function, UPPER, to the calculated field logic. It will ensure that Tableau will not consider any case-sensitive scenarios to the string. Once the changes are done, click on OK.
Now, you can see the email addresses containing .net are now deemed valid based on the revised logic in the Tableau view.
Important Things to Note
- Tableau contains a function that is case-sensitive and doesn’t require any wildcard characters. It assumes the wildcard character % at the beginning and end of the substring to be searched.
- This function can only be used in string fields or specific texts.
- You can create nested contains functions or use them with logical operators, such as IF, AND, OR, etc., to formulate complex calculations.
- If you intend to avoid too many search results from the contains function that is not the intended result, consider using NOT logic.
- For large datasets and multiple substring searches, consider using the IN operator instead of the contains function in Tableau.
Frequently Asked Questions (FAQs)
Yes, You can use the CONTAINS function in calculated fields to create fields that can be used in the Tableau visualization. For example, you can create a calculated field to identify all the shampoo products where the product description contains the keyword shampoo.
Yes, the CONTAINS function in Tableau is case-sensitive. For example, CONTAINS ([Customer Name],” John”) vs CONTAINS ([Customer Name],”john”) will be treated differently. Hence, you must understand the consequences of using the this function while performing Tableau calculations.
The CONTAINS function does a specific operation compared to other string functions in Tableau.
For example, the CONTAINS function returns a Boolean value of True/False depending on whether a it is a substring or not. In contrast, the FIND string function returns the index position of a substring within a string.
Similarly, other string functions such as LOWER and UPPER help you convert a string into lowercase or uppercase characters. LTRIM or RTRIM functions remove any leading or trailing spaces in a string.
So, in a nutshell, the difference between CONTAINS and other string functions is the purpose of usage and output.
You can’t use CONTAINS with multiple substrings. However, as a workaround, you can consider creating multiple calculated fields to meet your criteria or other operators, such as IN, to cater to such requirements.
Download Template
This article must be helpful to understand the Tableau Contains, with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What is the Tableau Contains Function. We explain how to use contains function in tableau with syntax, examples, and downloadable template. You can learn more from the following articles –
Leave a Reply