While not a regular occurrence, I have come across situations where a client wants to display a hidden sheet with an action filter but wants to enable different worksheets to activate the hidden sheet.
If the sheets are filtering on different fields when you set an action filter to exclude – so the sheet is hidden – it will only reappear when a user makes a selection in both of the sheets pointed at it and not just one.
In this blog, I am going to go over an alternative method that will give users the option to select one or both of the sheets used to trigger the action filter. I call it my “multi-trigger action filter” method. An additional feature with this method is that we can add in logic around how many items are selected. So, for example, the sheet could be set to only display when less than 15 items are selected or perhaps display if more than three items are selected.
Keep reading if you want to learn more about how this is done or skip to the bottom if you want to play with an example.
To break down this problem, I have put together a side-by-side example with the same three sheets Sales by Category, Sales by Segment and Sales by Month, used in both Examples 1 and 2. The goal here is to only display the sales by month view after a user makes a selection. Example 1 is done with just an action filter while Example 2 uses an action filter and a calculation.
Example 1: Action Filter Only
For the first example, I set Sales by Category 1 and Sales by Segment 1 as triggers on a selection to filter Sales by Month 1. Clearing the selection is set to Exclude all values:
Example 2: Action Filter and LOD
In the second example, I set Sales by Category 2 and Sales by Segment 2 as triggers on a selection to filter Sales by Month 2. The difference being that clearing the selection is set to Show all values. Note that there is currently nothing to hide the Sales by Month 2 sheet when nothing is selected:
To set this up so that the sheet will be hidden, we first need to activate the action filters displayed above so they can be used by the filter. Below is an example of a calculation to do this, which takes the distinct count of both Category and Segment and checks if less than three have been selected.
In this particular example, the calculation could work without the LOD brackets because the view displayed is rolled up. In cases where the records are broken out, the counts would require “{ }” placed around the count distinct functions.
The key with using “{ }” in this calculation is to realize that LOD calculations run before the action filters. So, to get the correct counts, I need to set the action filter to be in context so that the calculation run on the filtered amount.
To do this, I right-click on an action filter and select Add to Context. You can easily tell if this has been done correctly because the filter will change to a gray color. One restriction that does come with this method is that the sheet cannot be displayed when all options have been selected so that the logic to hide the sheet still works when nothing is selected.
Now, test out the vizzes below to see the difference in action:
To see a better use case of this trick check out the dashboard below where the users can display/filter a list of customers by selecting a combination of state or product sub-category.
