Jim Dehner 

 Tableau Visionary and Ambassador

Tableau How to’s,  Use Cases, and Forums Questions

Hey #DataFam

Let’s Talk

Open for

Post Links

Show – Hide Filters


Often we will see a Forum question on how to "Filter the view without filtering the underlying data?"  Really?   


What's going on? 


Load a data set into Tableau, open the Data Source tab, and look at the data – It is the complete data set 



Now create a new worksheet with a simple chart 



Open the Analysis drop-down and select View Data



The window that opens is the "Underlying Table" for the worksheet



The table is a subset of the full data set – 


In Tableau, each worksheet has a unique underlying table.  As you bring dimensions and measures to the rows, columns, and marks card they "Filter" the full data set – what is left is the underlay table for the worksheet and it is the only data from the data set you loaded available for that sheet.


So what – 


When you add filters to the viz it does affect the underlying table so it is not possible to "Filter the view without filtering the underlying data"  but it is possible to use a table calculation to Hide data from the viz.  


I like to think of it as creating a viewing window. Data that is in the window can be seen – that outside the window is hidden


Lookup() Function


This example uses Superstore data and presents the running total of Sales by Category over all the Months-Years in the data set – It looks like this



Now you only want to show the running total for 2019 but you want to include the data from 2016-2018 ("Filter the view without filtering the underlying data").  


Using Lookup() we will create a window based on the custom format at the year level of the Order Date 


Create a custom date on Year – we want to "Filter" by year – 


Open the Date pill then Create and then Custom Date



When the window opens Select Year from the Drop Down and the Date Part button (make the date discrete)



Now use Lookup – a table calculation – to read the year of each date in the table individually – This works much like a Lookup in excel – in words it says " from the Order Date Years measure – return the value in the current cell"  Note – the attr() all table calculation must be an aggregate



Then we add a conditional statement that will look at each value of the actual Order Date (Years)  either assign it as Show or Hide



Create the viz.  


The real order date goes on columns and the running sum of sales on rows


Add the Lookup Filter to the filter shelf and set it to "Show" and calculate across (or by Order Date)



and this is the result



The "window" is hiding January 2016 through December 2018 but allowing the 2019 data to be seen.  The date is not filtered out – the lookup filter window just hides the value you don't want to see


Last() Function 

Frequently a user wants to compare the latest sales to the prior year's same period – but they only want to see the last few weeks (or months). A solution is to use Last() – another table calculation – to create a dynamic viewing window –

To show only the last 6 weeks create a  "Last Show/Hide Filter" 


Adding a filter based on the last month date in the database makes the viz dynamic for the YoY calculation

Then create the viz


They can be any level of discrete date parts – add a year filter to only include current and last year and set the Show/Hide filter to Show and the window will include the last 6 data part values – here weeks 



The viz will dynamically adjust to the last 6 weeks when the database is updated  


These are just 2 examples there can be many more.


It is important to remember that table calculation are used to create the viewing window. They are at the bottom of the Order of Operation (a topic for another day) and work on the final fully filtered table that underlays the specific worksheet 


Hope this helps 



Leave a Reply

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