Last week, I was working on an accounting exercise in Tableau in which I needed to filter a trial balance for all accounts starting with “6” and “7.” Where I work, these are the accounts present on the profit and loss statement.
The first thing I did was look at the wildcard filter settings, since this is where we can easily filter for strings in Tableau (shown below). However, the wildcard setting won’t allow you to filter for two starting characters, which is what I needed. So I found a loophole.
Any time you find yourself needing to filter a dimension using a string-based filter, i.e CONTAIN(), STARTSWITH(), or ENDSWITH(), you can use the Condition filter instead. I’ve put some examples below. Here’s the important screenshot:
Make Sure You Know String Filters
This use-case comes up all the time. Filtering on partial strings is a huge skill, but since the notation in Tableau is distinctly different from Excel, most people take time to learn it. It’s super helpful, and I recommend you learn all the basics up front to save yourself frustration and time.
You can cover the essentials with our Tableau Quickstarter, or take the free [qsm_link id=1]Tableau knowledge quiz[/qsm_link] to see how much you know!
Tableau STARTSWITH Multiple Substrings Filter
We’re going to use the sample database “Superstore” that Tableau provides for free so you can better understand this article.
Let’s say we would like to isolate orders whose postal codes are located in Kentucky or California. Kentucky is preparing to get hit by a storm, and California just went through a major earthquake, so we want to assess the number of packages at risk for non-delivery.
In our dataset, we know that postal codes “9” and “4” are California and Kentucky, respectively. What we need to do now is filter the postal code dimension for only those starting with these numbers. Let’s try the Wildcard filter.
The problem here is that the Wildcard filter doesn’t allow you to put two characters on which to filter, as shown in the video above. So we have to find another way. But don’t worry, this is actually much easier than it looks.
To filter a string dimension using a string function for two or more strings, you need to use the Filter>Condition>By Formula field, where you place with STARTSWITH, CONTAINS or ENDSWITH and the logical operator AND or OR, depending on whether you want to filter for joint presence of the strings, or independent.
In our example, you would put STARTSWITH([Postal Code],”9″) OR STARTSWITH([Postal Code],”4″) as in the below picture.
However, you’ll note that Tableau won’t accept this formula because STARTSWITH is a string filter, and the Postal Code dimension is numeric, or an integer. Clicking “OK,” you’ll see this error message:
To fix this, we can have Tableau look at the Postal Code as a string using the STR() function:
Now you can hit “OK” and you’ll have only the Postal Codes that start with 9 and 4. This is how you filter a dimension on two substring values.
Tableau CONTAINS Multiple Substrings Filter
You can use this same technique for CONTAINS() and ENDSWITH(). All you need to do is decide how you want to join or separate the two substrings using OR or AND, and you can filter on as many substrings as you want!
