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

 

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


1 comment:

FAQ Series - Fiscal Date

Many businesses use a fiscal calendar for accounting and reporting . Their fiscal year can start on the first day of any month and may not a...