How to display data only when there are N or less data selected in the filter

Published: 06 Apr 2023
Last Modified Date: 07 Apr 2023

Question

How to display data only when there are N or less data selected in the filter.

<Example>
Suppose you have sales data for each manufacturer.
If 5 or less manufacturers are selected in the filter, the data is displayed.
User-added image
If 6 or more manufacturers are selected, the data is not displayed.

Environment

Tableau Desktop

Answer

There are 2 solutions.
Option1 : Using a table calculation filter
Option2 : Using a context filter

<Option 1 -Using a table calculation filter->
1. Connect to Sample Superstore from Tableau Desktop.
2. Drag [Sales] to Columns and [Manufacturer] to Rows.
3. Create a new parameter as following.
User-added image

4. Right click on [Number of possible displaying] -> Show Parameter
5. Create a new calculated field as following.
Name : Display Restrictions Filter(Option 1)
Calculation : IF WINDOW_SUM(COUNTD([Manufacturer])) <= [Number of possible displaying]
               THEN TRUE
               ELSE FALSE
               END
6. Drag [Manufacturer] to Filter pane -> Check in 5 or less manufacturers.
User-added image

7. Drag [Display Restrictions Filter(Option 1)] to Filter pane -> Check in "True"
User-added image

<Option 2 -Using a context filter->
1. Connect to Sample Superstore from Tableau Desktop.
2. Drag [Sales] to Columns and [Manufacturer] to Rows.
3. Create a new parameter as following.
User-added image

4. Right click on [Number of possible displaying] -> Show Parameter
5. Create a new calculated field as following.
Name : Display Restrictions Filter(Option 2)
Calculation : IF {FIXED:COUNTD([Manufacturer])} <= [Number of possible displaying]
               THEN TRUE
               ELSE FALSE
               END
6. Drag [Manufacturer] to Filter pane -> Check in 5 or less manufacturers.
User-added image