KNOWLEDGE BASE

Excluding Values from a Filter

Published: 22 Mar 2013
Last Modified Date: 24 Aug 2022

Question

How to limit the values available in a filter.

Environment

Tableau Desktop

Answer

The attached example workbook uses the sample data set Superstore to demonstrate creating a filter for [Product Container] that only has 3 container options.
 
CLICK TO EXPAND SOLUTION

Option 1: Use a Set as a Filter

  1. Right-click [Product Container] in the data pane, and select Create > Set…
  2. In the Create Set dialog, do the following and click OK:
    1. Check all options that should appear in the final filter card
    2. Name the Set something like Product Container Set
  3. Remove [Product Container] from the Filters shelf
  4. Drag [Product Container Set] to the Filter shelf
 
CLICK TO EXPAND SOLUTION

Option 2: Exclusion Filter Using Only Relevant Values 

  1. Right-click [Product Container] in the data pane, and select Duplicate
  2. Drag [Product Container (copy)] to the Filters shelf
  3. In the Filter dialog, check all options that should appear in the final filter card and click OK
  4. Right-click the Product Container card in the view and select Only Relevant Values
 
CLICK TO EXPAND SOLUTION

Option 3: Use a Parameter as a Filter (can be used with blended data)

  1. In the data pane, right-click the [Product Container] field and select Create > Parameter…
  2. Tableau Desktop will automatically populate the values of [Product Container] in the parameter. In the Create Parameter dialog box, do the following and click OK
    1. Add "All" to the List of values
    2. Remove all options that should not appear in the final filter card
  3. Right-click [Product Container Parameter] in the data pane and select Show Parameter Control
  4. Select Analysis > Create Calculated Field
  5. In the Calculated Field dialog box that opens, do the following, and then click OK:
    1. Name the calculated field. In this example, the calculated field is named "Product Container Filter Calc"
    2. In the formula field, create a calculation similar to the following:

      [Product Container] = [Product Container Parameter]
      OR [Product Container Parameter] = "All"
      OR ISNULL( [Product Container] )

      This calculation will filter the view to the product container selected in [Product Container Parameter], or all product containers if "All" is selected. If there are any NULL product containers, they will always be shown in the view
  6. Replace [Product Container]  on the Filters shelf with [Product Container Filter Calc]
  7. In the Filter dialog, check True and click OK
 
CLICK TO EXPAND SOLUTION

Option 4: Use a Set Action

Set Actions are a new feature added in Tableau Desktop 2019.1
  1. Rename the worksheet something like "worksheet to be filtered"
  2. Remove [Product Container] from the Filters shelf
  3. Right-click [Product Container] in the data pane and select Duplicate
  4. Drag [Product Container (copy)] to the Filters shelf
  5. In the Filters dialog, check all options that should show in the view
    Note: Steps 3-5 allow the view to show more options than the end can choose from. In this example, "Medium Box" always shows in the view.
  6. Right-click [Product Container] in the data pane and select Create > Set…
  7. Rename the set something like "Product Container Set" and click OK
  8. Create a new worksheet, named "filter worksheet"
  9. Drag [Product Container] to the Rows as well as to Color on the Marks card
  10. Drag [Product Container] to the Filters shelf
  11. In the Filter dialog, check only the options that the end user should be able to see
  12. Create a dashboard and drag both worksheets onto the dashboard
  13. Navigate to Dashboard > Actions…
  14. In the Action dialog select Add Action > Change Set Values…
  15. In the Set Action dialog, do the following:
    1. For Source Sheets, check only filter worksheet
    2. For Target Set, select Product Container Set
    3. For Clearing the select will, select Add all values to set
  16. Click OK twice to close all dialogs
  17. Navigate back to the "worksheet to be filtered" worksheet
  18. Select Analysis > Create Calculated Field
  19. In the Calculated Field dialog box that opens, do the following, and then click OK:
    1. Name the calculated field. In this example, the calculated field is named "Product Container Filter (set)"
    2. In the formula field, create a calculation similar to the following:

      [Product Container] = IF [Product Container Set] THEN [Product Container] END
      OR [Product Container] = "Medium Box"

      The above calculation will compare the dimension [Product Container] to the values of [Product Container] that are in the [Product Container Set]. The IF THEN statement is necessary because the [Product Container Set] only contains the values TRUE or FALSE.

      The OR [Product Container] = "Medium Box" clause means that "Medium Box" will always be TRUE, and thus will always show in the view.
  20. Drag [Product Container Filter (set)] to the Filters shelf
  21. In the Filter dialog, check True and click OK

Additional Information

Options 1 and 2 will also filter data out of the view.

Options 3 and 4 may or may not filter data out of the view depending on how the filter calculation is written.

Option 3 will also work if the filter comes from a secondary data source. Please note, only one value can be selected in a parameter at a time.

To view options 1 and 2 in action, see the video below: 



To voice your support for the inclusion of this enhancement in a future product release, add your vote to the following Community Ideas:
Hide quick filter to retain associated information
Hide legends while retaining their data



Discuss this article... Feedback Forum
Did this article resolve the issue?    
PRINT THIS PAGE
Trending Articles
Attachments
Limit Filter Options.twbx

All Support

SUPPORT BY PRODUCT
TOOLS & DOWNLOADS
INFORMATION
ASSISTANCE