Tuesday, 2 August 2016

How to filter data when you don't have the data?

Filters in Tableau are great, they let you get rid of data that you are not interested in a hone in on the data that you do. They do however have one big flaw. You can only create a filter if you have the data. What do I mean by this? Well lets look at an example. 

We want to create a sales dashboard per state. Our sales areas are divided by regions and we want to create sales dashboards one that just looks at just one region at a time Central,West,East and South.

So we could do that using a filter right? Well lets see what filter options we have. Dragging region onto the filter shelf shows us this

We only have 3 regions to choose from, its not possible to select the West region. The reason for that is that filters Only filter the values in our data. If the data isn't present, we cannot filter it out. We haven't yet had any sales for the West region, so we don't yet have that in our data, so we cannot create a filter on it yet. We do know though, that we will be getting data for it, so how can we set up filtering for data that we haven't got yet?

So what do we do in this instance? Well we can create the East/South/Central dashboard as those three regions are present in the data, but what about the West?

Well we could use and exclude filter and select East/South/Central to be excluded, leaving the other two regions. Then when we do get the sales for West arrive in the data the filter will still work and our dashboard will filter the data. 

But, and its a big but, the sort that Sir Mixalot would like, are we certain no more regions would ever be added? What would happen if a North region got created and data started to be associated with a North region? Well the East/South/West dashboards would be fine, its including the East,West and South regions so any new regions would just be ignored. However, what happens to the West? That filter is only excluding Central,East and South, any other value of Region is welcome. This means that the North region data will be included, silently. Using Exclude filters are only good if you don't care about extra data being added, sometimes that what you want, but its better to include if possible. 

So, theres the problem, how can we create a filter for data that isn't yet in our data source? The answer like most things is parameters. 

We need to create a parameter and then tell Tableau to match it to the Regions and then add that to the filters. 

First, lets create a Region parameter

Now we create a calculated field based on the value of the parameter. 

Add this to the filter shelf

and then test it out.

We can now select a region and the filter works. When we select West, we get a blank sheet, which is correct, there is no data, so nothing is going to be shown. However, when the West's data starts to get added this filter will become "active" and only show the wests data. 

Parameters are a great way to take control of your data viz, you can ensure that filters work how they should and that the only the data that you want to be seen, is seen. 


Author & Editor

Tableau Zen Master, Social Ambassador, Wrangler of Data, Vizzer of Data


  1. Thanks a lot Matt!! This was a really good post and it will certainly prove to be really useful to me. I'm sure that others will benefit form this as well.

  2. Good Information for this blog
    "Pressure Vessel Design Course is one of the courses offered by Sanjary Academy in Hyderabad. We have offer professional
    Engineering Course like Piping Design Course,QA / QC Course,document Controller course,pressure Vessel Design Course,
    Welding Inspector Course, Quality Management Course, #Safety officer course."
    Piping Design Course
    Piping Design Course in India­
    Piping Design Course in Hyderabad
    Welding Inspector Course
    Quality Management Course
    Quality Management Course in india
    Safety officer course

  3. Excellent information for this blog
    We are the best piping design course in Hyderabad, India. Sanjary academy Offers Piping Design Course and Best Piping Design Training Institute in Hyderabad. Piping Design Institute in India Piping Design Engineering.
    Piping Design Course
    Piping Design Course in india
    Piping Design Course in hyderabad

  4. Nice post. Find useful for data filter and management. Thanks for sharing informative content. Contact for responsive website design and custom design and development for your online business.

  5. Very nice post... Thanks for the amazing information.
    We offering a variety of services viz; Branding, Custom web design, Search Engine Optimization, Information Technology Project Management, Business Process Automation, Android & IOS Application development.
    Website design winnipeg
    Best SEO services winnipeg

  6. Envizon Studio is a leading Best web design company in Hyderabad offering all kinds of custom built websites, web portals & web applications. Please visit our website for more Information.

  7. This comment has been removed by the author.

    packaging printing