A Jedi (filter and table calc) trick

As I have been preparing the Filters, Filters and More Filters session (part of the Jedi Track) at #TCC12 I came across a neat and very useful trick using filters with table calculations. I will be using our Sample Superstore data to show you this trick.

Let’s say we would like to rank our Sub-Category of Products by Sales and have the ability to remove/hide certain Sub-Categories without this affecting our rank calculation or average reference line.

Well, this is not as straight-forward as it seems! Try it yourselves in the below viz, if you de-select the Office Machines from the filter, the rank and the average sale reference line will both recalculate.

This is actually working as intended as when we remove the item from the Quick Filter we are actually removing it from the query that Tableau sends to the datasource. Do not panic though, thanks to the Tableau Force we can use a filter and a Table Calculation to achieve exactly what we want.

Here are the results:

Now whenever I de-select a Sub-Category in the Quick Filter the item will be removed (or hidden) in the view and the rank and average reference line will not recalculate.

Why does this work? Well, I just took advantage of the fact that in Tableau, Table Calculations are performed locally (as opposed to being pushed to the datasource) which in this case means that Tableau will query for all of the Sub-Categories and then filter them. This is different to the first viz where Tableau queries the datasource only for the selected Sub-Category items.

So how did I do this? I will not go into all the step-by-step details on how this was done (you should attend #TCC12 :-) ) but basically I created a Table Calculation on the Sub-Category field as follows:

Note that in the above step different types of Table Calculations can be used (lookup, window_min. etc.) to achieve the same result.

Then I added this Table Calculation to the Level of Detail and then created the Quick Filter.

Emiliano Colosimo is a Solution Architect with Tableau's Professional Services team. Click here to see what sessions Emiliano and others will be giving at the Tableau Customer Conference.