Four Types of Filters in Tableau

Dimension filters, measure filters, discrete date filters, and continuous date filters

Create relevant analyses by keeping or excluding marks on a view using Tableau’s powerful filtering options. This video also covers how to apply these four primary filter types across worksheets and/or different data sources.

Hi. This is Ryan with Playfair Data TV, and in this video, we’re going to be discussing the four different types of filters that are available to you in Tableau. Those four different types of filters are a measure filter, a dimension filter, a continuous date filter, and a discrete date filter. To help illustrate what each of these four different types of filters does, I’ve got this simple bar chart that looks at our different customer names by their sales values, and they are colored by the segment dimension.

The first type of filter I’m going to show you is a dimension filter. The most intuitive way to create a dimension filter is probably just to select the dimension members that you want to either keep or exclude directly on your view. Just to throw out a real quick fake use case, let’s say that we want to exclude our top five customers. We’ll just pretend that they’re throwing off our scale, so we want to get them off the view.

I’m going to click Sean Miller. You can do a multi-select in Tableau by holding down the Shift key and choosing the last dimension member that you want to select. And if you hold the Shift key, it selects the first dimension member, the last dimension member, as well as everything in between. Now, if you hover over the dimension members, you’ll see that the first two choices are keep only and exclude. Those are going to do exactly what they sound like and create the first type of filter, a dimension filter.

So if I hover over those dimension members and click Exclude, those five dimension members have been removed from the view. You also see that something was added to the filters shelf. This is called the filters shelf because any dimension that you put on the filters shelf will filter the marks on the view. If you want to look under the hood and see what Tableau did for us when we excluded those five dimension members, you can click on the dimension and click Edit Filter.

And what it did was on this first tab, it went through these 793 customer names, and it selected some of them. Let’s see if I can find one. And it clicked exclude, this box to exclude them. That would have been very manual to do on our own. I’ll cancel and show you what it might have looked like. I’m going to drag customer name away to just start over with a clean slate where we started. And I’m going to drag customer name to the filters shelf.

In that first type of filtering that we did, if we were to create that manually, we would have to click None, go find the five customers that we wanted to exclude, click their names, and then click this Exclude button. That would’ve been very tedious. But there are three other tabs, and these are much more scalable. If I go to the Wildcard tab, this uses string functions to determine whether or not the dimension member will be kept on the view.

These are fairly intuitive. They do exactly what they sound like. You can type some letters in this box and choose to keep the dimension members or exclude the dimension members if it contains those letters, starts with those letters, ends with those letters, or exactly matches those letters. This is not case sensitive.

So let’s say that we wanted to do an analysis for all of our customer names that started with the letter C. I could click Starts With, type C in this box. And anytime you see the Apply button, you can preview that change before you accept it. So if I click Apply, I can see in the background there that, sure enough, the names that are left on the view all start with the letter C.

On the condition tab, I can put quantitative thresholds that determine whether or not the dimension member will be kept on the view. Again, fairly intuitive. You can choose any field, whether or not it’s on the view. I’ll just stick with sales for now. You can choose an aggregation of that measure, and you can choose an operator. We’ll say that the sales have to be greater than or equal to 5,000. I’ll click Apply again.

And we can see our list in the background there getting shorter. But what we’re left with are all of the customer names that start with the letter C and their sales were at least $5,000. Notice, both of those criteria had to be true in order for the dimension member to be kept on the view. At any time, if you want to review all the logic that you’ve coded across a dimension filter, you can navigate back to the General tab.

There’s a box here at the bottom that says summary. It says the field that we’re filtering on is called customer name. On the Selection or General tab, it’s another name for it, we didn’t change anything so far. There were 793 out of 793 customer names left in the view. On the Wildcard tab, we said that the names had to start with a C. And on the Condition tab, we said that the sum of sales has to be greater than or equal to $5,000.

All of that criteria across all the tabs has to be true in order for the dimension member to be kept on the view. The top tab is slightly different. So I’m going to cancel this and start over one more time by dragging customer name away. And I’ll put customer name back on the filters shelf and navigate to the top tab.

On this tab, you can tell Tableau to keep a top N as well as bottom N number of values on the view. That’s the proper name for it. Most people think of it as a top 10 or a bottom 10. In fact, that is the default, but you can use any number you want. To illustrate this, I’m going to click by field. I’ll show you the default is top 10 by sum of sales. If I click Apply, what we’re left with are the top 10 dimension members on the view.

I had already selected those five, which is why they’re highlighted. Let me go deselect that. Jump back into the top tab. You can also toggle from top to bottom. So these are your 10 worst performers in terms of sum of sales. And again, 10 is the default, but you can type any number you want into this box. If you wanted to see your top or bottom five customer names, you could replace the 10 with a 5, click Apply, and there’s your bottom five.

I’m going to click Cancel, drag this away to show you the next type of filter, which is a measure filter. A measure filter is similar to the Condition tab of a dimension filter in that you can code quantitative thresholds that determine whether or not the dimension member is kept on the view. I will put sales onto the filters shelf and show you how this one works.

The first difference that you see is before it opens up the filter dialog box, we have to choose an aggregation for that measure. I typically just go with the default aggregation. So we’re using sales, and I’ll choose sum. Click Next. Notice that a measure filter also has four different tabs on it, but they are much simpler than the dimension options. They’re fairly intuitive. The default is range of values, and that’s usually what I stick with, because it allows me to choose both the minimum threshold as well as the maximum threshold.

You could also toggle that to just a minimum threshold, just a maximum threshold. And then the special tab is to handle null versus non-null values. Just to illustrate this once, I’ll say let’s keep everybody that has between $10,000 and $20,000 of sales. If I click the Apply button, you’ll see that all the names left on the view had between $10,000 and $20,000 worth of sales.

The next type of filter is a discrete date filter. I’m going to drag order date to the filters shelf. And essentially, if you choose any of the date parts that have a blue icon next to them, you’re creating a discrete date filter. So if I were to choose years and click Next, I have these discrete options to choose from. If I choose 2018 and 2017 and click Apply. What I’m left with are the customer names that had a row in the data set in the years 2017 or 2018.

A continuous date filter will appear if you choose any of the green options. I’m going to start with range of dates. And four out of these five tabs for a continuous date filter are very similar to a measure filter. So the default, when you choose range of dates, Tableau allows you to set both the minimum date as well as the maximum date. But you can also toggle that to just choose the beginning date, just the end date. And then, again, the special tab is to handle null versus non-null values.

Those are fairly intuitive, but there is also a special type of continuous date filter called relative date. This date will be relative to the time on your computer to start with. But you can set the anchor, which is right here, to something different. But this is relevant if you’re wanting to have your Tableau reports set to a relative date when you first open it up. A common scenario is if you get in on a Monday morning and you want to have your Tableau dashboard show you the last week of data as soon as you open it.

If you wanted to set up a similar experience, you could choose last and bump this up to seven days. Days was the default date part. Notice, you can definitely choose other date parts. But in this scenario, you might say, I want to see the last week of data. So I picked days as my date part, and I bumped that up to seven.

Notice, Tableau is giving me a summary of the dates that will be kept on the view. I am recording this video on October 19, 2018. So this is a very important thing to point out. That relative date includes today’s date. But most companies don’t update their data more than once per day. Usually, that happens overnight. Your ETL process will run. You’ll have fresh data in the morning.

If you’re in a similar scenario, just remember that this seven-day date range will include today. So the data for today will likely be empty. If you’re in a similar situation, just bump this up to eight. Because it’ll include today, there’ll be no data, and then you’ll have the seven days leading up to the current day.

Filters are very powerful, as you’ve seen how quickly, in just a few minutes, we were able to execute these four different types of filters. But they become even more powerful when you transfer their control to an end user, which you can do by doing what’s called show filter. Let’s say we wanted to allow our end user to choose which segment was being visualized on the view. I can right-click on the segment dimension and click Show Filter, and it will appear in the top right corner. You’ll also see that a filter was added to the filters shelf.

I could have also got here if I already had the segment filter on my filters shelf. I’ll go ahead and use all for now. I could click on that filter from the filters shelf and click Show Filter from there, as well, even though it’d already been added to the view. Now, if I deselect consumer, you’ll see all of the blue bars are excluded from the view.

And my end user, now that I’ve shown the filter, would have access to this filter in any interactive version of Tableau, including Tableau Reader, Tableau Public, Tableau Server, or Tableau Online. You can always double-check the user experience by going to presentation mode. It’s this projector looking icon about halfway over on the top navigation. If I click on that, the authoring interface gets stripped away. And what I’m left with is exactly what my end users will see.

This is a really handy way to test out the user experience. You can make sure you’re showing the filters that you want to show. You can hover over, see what the tooltips look like. And you’re walking in your end users’ shoes. You’re going to see exactly what they’re seeing. To get out of presentation mode, simply click the Escape key, and the authoring interface will come back to you.

One last thing about filters, they are only applied to the sheet that they are added to by default. So I’ve only got one sheet so far in this practice workbook. If I were to throw together a quick bar chart that looked at sales by segment on a second sheet and navigate back here to sheet one, notice the segment filter is only on sheet one. If I go to sheet two, there is no filter taking place.

But you can apply these filters to other worksheets by right-clicking on the filter and hovering over Apply to Worksheets. These go in order from most powerful to least powerful. So, again, there’s the default. That’s what’s been selected, only this worksheet. If I chose Selected Worksheets, it would open up a window that showed me all of the sheets in this workbook. I’ve only got two, so those are the two represented.

If I applied this filter to sheet two and clicked OK and navigated back to sheet two, notice we’ve only got two bars instead of three. We also see the segment filter has been added to the filters shelf of sheet two. Back here on sheet one, to explain the other two types of worksheets you can apply this to, the second one down is called all using this data source. This will make this a global filter at the data source level.

I’m only connecting to one data source at the moment, so this would actually make this a global filter and apply to my two worksheets in the workbook. The last one is called all using related data sources. This makes this a global filter, even across different data sources, as long as they have that dimension in common. So if I was connecting to a secondary data source that also contained a column called segment, this filter would be applied, even across data sources. That’s very, very powerful.

Before Tableau added this feature, that would require some pretty expensive software in order to do that type of manipulation in applying those types of relationships. One last thing on these filters, once they’ve been applied to multiple worksheets. I will show the filter on sheet two, as well. What I think is really nice about this is you can change the settings of the filter on either of these sheets or any of the sheets that that filter is applied to, and it will then filter across the workbook.

So if I wanted to get consumer back on the view, I could click Consumer on the sheet two filter. And now, when I navigate back to sheet one, the blue bars have returned.

This has been Ryan with Playfair Data TV – thanks for watching!