In this video I will teach you how to perform a multiple keyword search in Tableau. I’ll show you how to perform both an OR search & an AND search against keywords you type into your dashboard parameter.

In order to accomplish these tasks, we need to use regular expressions. As I mention in the video, regular expressions can be indecipherable, akin to hieroglyphics or something out of a Dan Brown novel to the average person. You can go find this information on the Tableau knowledge base, but I want you to be the person that understands WHY something works as opposed to just copying and pasting a solution that you cannot explain to yourself or others.

The use case for multiple keyword search is for those times when you need to filter your data by a comment, description or other free form blocks of text that are not necessarily subject to the most stringent data governance rules, because let’s face it, these types of fields exist in abundance. In the video I create a dashboard that illustrates the power that this type of search can bring to your dashboard.

You can interact with the dashboard from the video on my Tableau Public page.

OR SEARCH

1. CREATE PARAMETER

Create a parameter named Search Terms.

• Data type: String
• Current value: keep it blank.
• Allowable values: All

2. CREATE CALCULATED FIELD 1

Create a calculated field named Regex String (OR) including the following formula:

"(" + REPLACE([Search Terms], ',' ,'|') + ")"

Let’s breakdown what is going on with the apparent gibberish seen above (there is a method to the madness here). I’ll assume you want to perform a search against a field that contains the terms “paper” OR “Paper”; the case of the spelling is a factor in the results (case sensitive). To adhere to proper regex formatting, you could write a valid expression as such:

(paper|Paper)

This pattern indicates that you want to return a match on the characters “paper” OR “Paper” literally. You can test this out at https://regex101.com/

Although for some reason the official Tableau knowledge base article says to use the REGEX_REPLACE formula in lieu of REGEX, it is not needed since we are doing a simple replace and not using a regex formula to aid our replacements.

In the Tableau calculated field above, we are using the REPLACE function against the parameter named “[Search Terms]” to replace every instance of a comma with the the ‘|’ pipe symbol.

Think about it, if you type the terms “paper, Paper” into the a dashboard’s search term parameter as seen below, the calculated field will reformat your list into the proper regex format by replacing the comma with a pipe and then enclosing the term within parenthesis; i.e., (paper|Paper).

3. CREATE CALCULATED FIELD 2

Next create a calculated field named Regex OR Filter that contains the following formula:

REGEXP_MATCH([Product Name],[Regex String (OR)])

Let’s breakdown what is going on with the formula seen above. The REGEXP_MATCH formula is evaluating a properly formatted regex expression which is contained within the [Regex String (OR)] calculated field we created in step 2. This calculated field will contain the formatted expression (paper|Paper).

REGEXP_MATCH returns TRUE if a substring of the specified string matches the regex pattern. In our case

[Regex String (OR)] = (paper|Paper)

Thus the REGEXP_MATCH function will evaluate the regex expression (paper|Paper) from the second argument against the [Product Name] field in the first argument. The function will return either TRUE or FALSE depending upon whether a match is located within this field.

3. DRAG FIELDS TO ROWS AND FILTERS SHELF

Place [Product Name] to rows and [Regex OR Filter] to filters, then select True.

AND SEARCH

In order to create the AND search for your dashboard, you will follow very similar steps to the OR search. 1. Create the same parameter named Search Terms as in the OR Search above.

2. CREATE CALCULATED FIELD 1

Next create a calculated field named Regex AND Filter including the following formula:

"(?=.*" + REPLACE([Search Terms], ',' ,")(?=.*") + ").*"

This may look like something out of a Dan Brown novel, but don’t let it intimidate you. In a similar fashion to the OR search where we replaced commas with a pipe symbol, we are simply replacing commas with the following characters between the double quotes “)(?=.*”

The proper regex pattern to indicate an AND search against the terms paper and Paper within a body of text would be as such:

(?=.paper)(?=.Paper).*

Knowing the proper regex format should make the contents of the calculated field above more clear. We are using the REPLACE function against the parameter named [Search Terms] to replace every instance of a comma with the the “)(?=.*” symbols. Thus “Paper, paper” values typed into the [Search Terms] parameter becomes reformatted into(?=.paper)(?=.Paper).*

Placing .* at the end of the string means that any combination of characters can be placed after the search terms and still yield a TRUE result.

3. CREATE CALCULATED FIELD 2

Next create a calculated field named Regex AND Filter including the following formula:

REGEXP_MATCH([Product Name],[Regex String (AND)])

4. DRAG FIELDS TO ROWS AND FILTERS SHELF

Then place [Product Name] to rows and [Regex OR Filter] to filters, then select True.

CONCLUSION

That wasn’t so horrible was it? If you’re a visual learner like me, then go ahead and watch the video to hammer home the concepts. Hopefully this post will help you understand how these regex patterns work and how they can add value to your next dashboard design! As always if you learned something drop a comment on the YouTube video and let me know.

Please like and subscribe on the Anthony B. Smoak YouTube channel.

All views and opinions are solely my own and do not necessarily reflect those of my employer

I appreciate everyone who has supported this blog and my YouTube channel via merch. Please click here

Thank you!!

Anthony B Smoak

TLDR: Tableau Knowledge Base Reference: https://kb.tableau.com/articles/howto/how-to-filter-data-by-wildcard-search-with-multiple-keywords