A Jedi (Filter and Table Calc) Trick

By Emiliano Colosimo 11 Set, 2012

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.


Submitted by Joe M. on


Thank you for the write-up on this frequently used technique.

I would like to add to your "Why does this work?" paragraph. I agree that table calculations happen after the query result is returned from the data source, although in my opinion, the fact that all table calculations will be evaluated before filters on table calculations are be applied is the specific aspect this technique is taking advantage of.

Some interesting tidbits in regards to LOOKUP(MIN([Sub-Category]),0)
- Because the offset argument for the LOOKUP() function is 0, all possible compute using settings will produce the same result.
- Replaceing MIN() with MAX() or ATTR() will produce the same result because Sub-Category is activly on the worksheet as a dimension impacting the level of detail.

Although, I fail to see how WINDOW_MIN() in place of LOOKUP() would produce the same result without additional work. Using WINDOW_MIN() would require another dimension to add partitioning to produce the same result.

In your workbook, the table calculation the reference line is based on is:


In my opinion, this is a less than ideal formula. Yes it works, and it is explicitly performing an average, but I do not think there is a situation where this would be an ideal formula for the result you are looking for in the situation of this view. The added complexity may have a place in a different situation.

A more ideal formula for this specific situation would be:


This is both easier to read, better for maintainability, and asks Tableau and the data source for fewer operations, potentially improving the performance.

Additionally, although outside the scope of this situation, if your number marks in your partition was enough to cause a performance degradation, then a formula like:


would have a better performance, at the cost of readability. This performance issue has been well documented and is one that I hope Tableau addresses in a future release.

Yes, all these routes produce the same result, and that is one of the really amazing things about Tableau, there is always more than one way to get to the desired result. In light of this, I believe it is important to be aware of the availability and effects of each possible route, in this case a varying effect on performance and readability.

In regards to Rank on the color shelf, that is not useful for data visualization designed with human cognition in mind.

Do you really want Sub-Category to be in a hierarchy for this view? I know it was the default, but why not remove the hierarchy for this demonstration?

In my opinion, this blog post was an attempt to demonstrate a technique using a table calculation as a filter, the idea was sound, but the execution was less than ideal.

If you are going to take the time to make an example, why not make it a good example?

Here is a more focued example: http://public.tableausoftware.com/views/FilterTrick_WithCalcjmedit/Dashboard

Submitted by Thierry J. on

Perfect timing for this blog post Emiliano.

I have exactly the same need and was recommended to use the method you explain here (thanks again Andrew!)

However, I can't make such calculated field work as a Global Filter.
See my forum post here: http://community.tableausoftware.com/thread/120002

Hopefully you or Mighty Joe can find a workaround :)


Submitted by Joe M. on


Jonathan's reply in your thread explores the only possibilities that I know of.

non-humans click here