<< Back

Multiple value wildcard search filter in Tableau

One of the many great aspects of being a Tableau Consultant is that we can help clients answer challenging questions. We recently received a question: is searching for multiple strings in a single dimension while choosing if we wanted an ‘AND’ or ‘OR’ relationship between those items as well as adding a key phrase to exclude from the search. Practically think of this exercise as a multiple and customised wildcard match. While Tableau has excellent and flexible filtering capabilities, there is no canned solution for this requirement. What are we talking about exactly?

– Searching on up to 3 key phrases

– Full user control on whether the dimension contains ‘all key phrases’, ‘any of them’ or ‘two at the same time or the third one’, via logical operators

– Optional exclusion of one key phrase


Background to the challenge

Before jumping into discussing the execution, we have to be aware that there are already similar solutions around in the community. I have found one from Ben Neville who built a custom wildcard match filter with up to three key phrases. The search logic between them is ‘either-or’. What I worked on extends its scope.

We at The Information Lab are also committed advocates of Tableau and grab every opportunity to pass on our understanding of the tool. As the solution in this post is built on wildcard match logic, let’s check what is already available in Tableau!

Tableau Wildcard Filter

The wildcard match options support a single key phrase. There is a discussion in the Tableau community about applying regular expressions in a wildcard match value but as of now we have to find a different way.


Alternative paths to the solution

What do we know in advance? We are dealing with Tableau, so:

– everything is possible

– there are multiple ways to achieve the objective

– speed and user experience are two key aspects of the software so we keep this in mind

Tableau’s wildcard match value entry can be mimicked by a string parameter and the CONTAINS(string, substring) function is just what we need during the search. The difference between two available alternatives is how the search phrases are entered.

In method #1 the user is asked to enter all the key phrases as one parameter value. Search phrases could be separated by e.g. comma and special characters can indicate the logical relations between them. For example “”= OR, “&”=AND, “-“=Exclude. A search expression like: return every dimension member that contains “Item” AND “32” OR “System” excluding “blue” would be entered like: Item, &32, System, -blue. Then this expression has to be parsed, also checking the first character of words, ‘registering’ their meaning and if “& / -” are present, trimming them. This process requires very complex string calculations that may hinder performance. Moreover, asking the users to remember to a list of special symbols and apply them to compile their search phrase is not exactly user friendly. So I rejected this route and chose method #2. There we create separate parameters for every key phrase as well for the logical operators to link them together. It speeds up development and easier to overview from the user’s perspective.


Detailed steps – building blocks

What are the ‘ingredients’ to this exercise? Take 4 string parameters (3 for search phrases to include, 1 to exclude a search phrase) and another 3 parameters to describe the logical operators (connections) between them. Actually, these last 3 parameters are just 2 as I defined the exclusion as always an ‘AND’ on top of the included key phrases. Logical operators are AND / OR though XOR (=exclusive OR) could also be included. That would be an interesting addition to this solution but for now I kept this simpler. Examples of the parameters:

Search phrase parameter  Boolean operator parameter

To recap, entering this way a multiple value wildcard search phrase looks like the following:

contains “Phrase 1” AND/OR contains “Phrase 2” AND/OR contains “Phrase 3” AND exclude “Phrase 4”


Detailed steps – calculations

This exercise is perfect to demonstrate the thinking process behind writing more complex Tableau calculations. The end result we strive for is a single calculated field that accomodates the combination of the above elements and filters for those dimension members where the overall calculation value is true. What are the combinations of logical relations among the 4 items of the total search phrase?

1. AND + AND + AND

2. AND + OR + AND

3. OR + OR + AND

4. OR + AND + AND

The last AND is for the optional exclusion of the 4th search phrase. (To keep this simple, I omitted the XOR logical connection everywhere and the OR link between the “contains” and “excludes” parts of the multiple value search.)

Other points to consider: how to deal with empty search phrases? How do we want the expression to be evaluated? We use brackets to make sure the OR/AND logical operations are evaluated from left to right.

I hope you are still with me, I know it is high time to actually seeing some formulae… So let’s start with the “Exclude” formula linked to the 4th search phrase parameter I called “Exclude phrase”.

Exclude formula

Don’t worry now about why I channeled to formula’s result to -50 or 100. I could have chosen any number, for our purposes I wanted the “False” value of this formula (that is actually the “True” value of the exclusion – so the Exclude phrase is NOT contained in the dimension member!) to be ‘visibly’ higher than the “True” value. Nothing special so far, we are using the CONTAINS function to determine whether the Product Name dimension contains the Exclude phrase string. As usual, the example is based on the Superstore Sales data set, shipped with Tableau Desktop.

Now we can move on to each of the four formulae reflecting the logical combinations between the search phrases.

AND-AND-AND formula

AND-OR-AND formula

OR-OR-AND formula

OR-AND-AND formula


You may have noticed that I also check if the key phrase is empty or not. In case of “OR” conditions the lack of this check would return all the dimension members (all contain “”). Also, if all the “contains” search phrases are left empty, that side of the search should return all the Product Names.

It is time to combine these items to arrive to the final field we will rely on to determine whether a Product Name is displayed or not. Remember, in the boolean operator parameters 0 = AND, 1 = OR. The 1st boolean operator parameter connects the 1st key phrase and the 2nd key phrase. The 2nd boolean operator connects the 2nd key phrase with the 3rd key phrase (actually, the result of the 1st and 2nd with the 3rd). There is no 3rd boolean operator parameter required as the “exclusion” is always “added on top”.

Search is True formula

It comes handy now that the four calculated fields (AND-AND-AND, etc.) are not boolean fields as those could not be used in a logical IF-THEN-END clause. Attaching numbers to the partial outcomes (the contain / exclude parts)  and deriving a final True or False outcome supported his.


Finishing steps for the complete, user-facing solution

Our work has not finished yet but we are almost there. In a view with the “Product Name” field, the “Search is True” field has to be placed on the Filters shelf with filtering for only the “True” outcomes.

Search is True field on Filters shelf

Finally, all the parameter controls have to be displayed in a sequential layout on a dashboard together with the view with Product Names. To test the solution, please click on the image below. I welcome any comments.

Multiple value wildcard search dashboard

10 thoughts on “Multiple value wildcard search filter in Tableau

  1. I am keen on replicating this for a small work item I have in hand to search multiple strings. I downloaded the workbook as I have Tableau server but the data source file is Sample – Superstore.xls is not available. I wanted to edit the information and see if it works for my case.

    If that is not possible at least a guide on how you did it in Tableau step by step.

  2. Agree Awesome! We have successfully integrated this into our Dashboard. One question, do you have any suggestions on how to “clear” the string parameter entries to the empty, default state? I am able to do this with our filters, but not the string parameters.

    Thanks in advance.

  3. I believe this shows what I need to make it work, however I would like to know if I can do this type of execise via calcualted fields. So I have an excel file with a column with text. I want to be able to search that column based on certain parameters and then label it into a specific “bucket” label. Below and example of the terminology I am looking to filter but don’t know how the terminology will work on a If and else statement.
    Here is how I want to filter out to specific Label by word searching an excel column:

    (“eng” or “eng1” or “eng2” or “eng3” or “eng4” or “engine” or “engines” ) and (“shut down” or “shutdown” or “IFSD”) and (“atb” or NEAR((“emerg*”, “declar*”), 2, FALSE) or “diver*” or “turn back” or “turnback” or “turned back” or “mayday” or “may day” or “21.3”) and not (NEAR((“no”, “shut*”), 2, TRUE) or NEAR((“not”, “shut*”), 2, TRUE) or NEAR((“apu”, “shut*”), 2, FALSE) or NEAR((“no”, “emerg*”), 2, TRUE))

  4. Hi, this is great but it does not run a proper boolean search for the combination AND OR (AND EXCLUDE). Is there any fix to this?

  5. You mean well as a Tableau consultant, but this is a fantastic example of how fantastically bad Tableau is, with regard to what should be basic functionality in the 21st Century. Excel has better filtering, searching, conditional formatting, and sorting. Why not emulate it in every way, Tableau devs? It would be well for Tableau to just focus on presentation and give up on data processing, or ramp up their game with functions that work. The workforce needs fewer tools, but more powerful ones. It’s a tragedy that knowledge workers are wasting their time with workarounds like this to make a visualizer work as a data processor, when their employers need them to do more meaningful work.

  6. Thanks for your suggestion. I attempted this solution, but ran into a hitch. As of 10-21-21, it seems that the [exclude] function no longer exists in tableau. That word is defined for LOD calculations. So how would we update this solution?

Leave a Reply

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