In Tableau, the sequence that filters or calculations are executed is controlled by the Order of Operations – but how does that affect your calculations

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 before 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 a LOD with Superstore data using a Fixed expression that combines segments and categories:

 

 

And a very simple text table viz:

 

 

Apply a filter on Region and see the effect of the context filter

 

First, filter out the 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 require the use of a LOD and a Ranking function –

 

And

 

 

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 :

 https://public.tableau.com/profile/jim.dehner#!/vizhome/ContexFilters/ContextFilters

Jim