This post on the Tableau forums led me to figure out a new trick with Quick Filters. The goal is to know whether a Quick Filter has (All) Customers selected or some subset of Customers, then return a different measure based on that flag. Just to make it a little more fun, we want this calculation to work when there are other filters present, or not, to look something like the following view based on the Superstore Sales data:
Tableau does not give us visibility into the Quick Filter status, however for discrete dimensions we can deduce that via other calculations. The method I came up with looks at how many members of the dimension are remaining, i.e. how many Customers have been selected, and compares that to the total number of customers. If the two numbers are the same then (All) customers must be selected, and with that information then we can choose which measure to present.
For this example, the Quick Filter is on Customer, a discrete dimension. Tableau processes discrete dimension filters in the query to the data source, so after that data is returned to Tableau we have no way of knowing how many distinct customers there are in total. One way around this would be to separately calculate the total number of customers and hard-code it via a calculated field or parameter, but that would be static and need to change every time a new customer was added. An alternative would be to compute that total in a subquery that would be part of the data source, but it would be really hard to make that work are other filters in the view. Plus, if I solved it outside of Tableau then I wouldn’t have had much reason for this blog post!
So, where can we get a source of data that has the total number of customers before the Customer filter is applied, but does include other filters, like a filter on Customer Segment? The answer is to use a self-join via a Tableau data blend. The reason why this can work is that we can control the relationships in the data blend, and therefore where filters are applied.
When the dimension being filtered is in the view (on Rows, Columns, Pages, or Marks Card) and has a an active relationship (in v7, having a defined relationship, in v8 having a defined relationship and the linking icon turned on) Tableau will filter both the primary and secondary. In all other cases, Tableau will only filter the primary and not the secondary. Here’s a handy matrix:
By turning off the relationship on Customer between our primary and secondary data sources, when we filter on Customer only the primary gets filtered, leaving the total set of Customers we can count for our test. To remove that relationship, in version 7 go to Data->Edit Relationships…, then in the Relationships dialog click on the Custom radio button to delete the Customer relationship:
And by leaving the relationship in place on Customer Segment, then both the primary and secondary are filtered on Customer Segment, as in the following view:
Now we have the working totals and need to build the calculation to test whether (All) is selected or not. Since we want to have the calculation work across all the dimensions in the view, we resort to table calculations. Here’s the calculation formula, called “All Customer Flag”:
This has an Advanced Compute using of every dimension in the view in order to address on every dimension and therefore have only one partition (all data in the view). For the following view, that will be Customer Segment, Order Date, and Region. In this case, since we’re using TOTAL() the order doesn’t matter.
In the worksheet below, you can filter Customer Segment and the status of the All Customer Flag won’t change, but when you filter out any Customers the flag will change:
On to the measure… For this example, what if we don’t want to aggregate the final view by Customer Segment, and instead just want it to be a filter? In other words, the view is at a finer level of granularity than we need. To deal with this situation, we again turn to a table calculation, called Adjusted Sales:
IF FIRST()==0 THEN IF [All Customer Flag] THEN TOTAL(SUM([Sales])) ELSE TOTAL(SUM([Sales])) * 1.2 END END
This is a nested table calculation. The IF FIRST()==0 part reduces the number of records returned. Then the IF [All Customer Flag] returns the total sales when all Customers are selected, and the total sales plus 20% when only certain customers are selected. The Compute Using for the nested Adjusted Sales is set to Customer Segment, so it partitions on Region and Order Date.
Now you can see this in the view, I added a couple of calculations and subtotals to show how the Adjusted Sales matches the Sales from Primary when all Customers are selected, and matches the Sales from Primary +20% when fewer customers are selected.
After that, we can turn that into the final view by moving Customer Segment onto the Level of Detail, getting rid of the validation calcs, and putting a copy of the Adjusted Sales calc on the Filters Shelf to filter for non-Null values. This can speed up the view when there are a lot of marks, and is useful in version 8 to avoid unwanted mark stacking.
In the Tableau Public workbook above, click over to the Final View tab to see it all in action.