This post is about controlling whether to display data based on user filter selections. For example, a dashboard may have a number of hierarchical filters, and the intention is for the dashboard only to return data when at the bottom of the hierarchy.
This was inspired by a post on the Tableau Forum asking how to display nothing on a dashboard until the lowest level of detail is chosen from a number of cascading quick filters.
We can use the Tableau Size function for this.
In this case the questioner wanted a blank dashboard until the selection of a single City, with the user navigating the filters from Region > State > City.
Use SIZE to control whether to display
This is possible using the SIZE() function. Using ‘Compute Using’ enables the SIZE function to count the number of selections of a given dimension. For example it would return 2 on selection of 2 items.
Follow the below steps to show data on selection of only one item:
1. Set up the worksheet with the required data and filters
2. Create a calculated field called Size. The formula should be one word, SIZE().
3. Drag the City on to the level of detail shelf; we need this to calculate the number of selected cities.
4. In this example, with Category and City in the view, pay attention to the advanced table calculation settings. ‘Address’ Category and City in the Compute Using area. As City is the level defining whether to display the data, set City ‘At the level’.
5. Put the Size field to Filters ensuring “Compute Using” is set as above.
6. Set the ‘At most’ of the filter to be 1. In other words with the selection of only one city show the data, otherwise show no data.
Simple as that!
The City filter could also be a single select filter. It is here as multi select to show the Size function in operation.
Hi Andrew, nice solution!
An alternative that I’ve begun to use for situations like this is LOD expressions. In this example {EXCLUDE [Region], [State], [City] : COUNTD([City])} does the same as the SIZE calc without requiring knowing about addressing or having to put City on the level of Detail of the view. The tradeoffs are that the table calc requires knowing about addressing can introduce unwanted densification while the LOD can introduce an additional subquery that can be performance-prohibitive.
I published the alternative at https://public.tableau.com/views/displayonlyonewithLODexpression/EXCLUDEforSingleCitySelect?:embed=y&:display_count=yes&:showTabs=y.
Jonathan
That’s a nice solution as well Jonathan, thanks for sharing.