Sunday, June 21, 2020

FAQ Series - Context filters - What do they really do?

In Tableau , like other languages, the sequence that filters or calculations are executed is controlled by the Order of Operations. The order starts with 3 filters – Extract filters limit the data passing from the data source to tableau, Data Source filters further limit data available in the workbook and can be specific to values within dimensions, and Context filters limit the data available to the remainder of the calculations, sets, Top N, dimension or measure filters or table calculations


But how exactly do context filters affect Top N and LOD expressions


The Context filters are applied in the step prior to creating sets, setting Top N filters and performing LOD calculations.  When a dimension is “placed in Context” and then the values are only the “un-filtered” data is available in the LOD or the Top N calculations


Let’s look at an example using an LOD with Superstore data using a Fixed expression that combines segments and category:



And a very simple text table viz:


Now apply a filter on Region and see the affect of the context filter


First filter out Central region with a normal dimension filter (after the context filter and the LOD expression are applied).  The results have not changed – the filter did not affect the LOD results (Often referred to as “Ignoring the filter”) 

Now place the region filter in Context so it is applied before the LOD is calculated:  And as expected the results from the LOD are now changed (they don’t include the Central region that was filtered out by the Context Region filter)


Top N Filters


The Top N filter option on sets and dimensional filters are also affected by the placement of a dimension in Context


Superstore data will be used in the example and the Top N option will be used on Sub-categories




Applying the filter on a text table and using year on columns results in


Out of context the East region is removed but the Top 3 sub-categories are unchanged -  That is because the Top N were determined before the dimension filter was applied


When Region is placed in context the East region is again removed but Tables now replace Storage – The Top 3 Total across the 3 regions are now Chairs, Phones and Tables – the East region was filtered out of the data in the Context filter before the Top N was determined.  Note also the values or Chairs and Phones in the other regions remains unchanged - 


The total across the 3 regions is calculated for each sub-category and it is the 3-region total that is used to determine the Top N

That may not be what you wanted – How would you determine the Top N subcategories based on their sales in each Region independently

That solution will required the use on an LOD and a Ranking function –




And a Filter based on the Rank




The LOD determines the sub-category sales in each region and then the Rank (a table calculation ) is applied



And filtering the East region removes if from the chart without affecting the top 3 determined by the rank in the other regions



Placing a dimension in the Context filter is a decision that you control – it changes the order in which filters are applied to the data table and the calculation results.  Make your decision on how you want the filter to act.

It may take a little practice, but you will get there in no time.

A downloadable workbook containing these examples can be found at :!/vizhome/ContexFilters/ContextFilters  


1 comment:

Note: Only a member of this blog may post a comment.

FAQ Series - Drilling down in Top N and Other

Frequently we see questions on how identify the Top N and group the "Other". A common problem with many business applications - de...