Excluding Values from a Filter

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

CLICK TO EXPAND SOLUTION

Option 2: Exclusion Filter Using Only Relevant Values

CLICK TO EXPAND SOLUTION

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

CLICK TO EXPAND SOLUTION

Option 4: Use a Set Action

Set Actions are a new feature added in Tableau Desktop 2019.1

Rename the worksheet something like "worksheet to be filtered"
Remove [Product Container] from the Filters shelf
Right-click [Product Container] in the data pane and select Duplicate
Drag [Product Container (copy)] to the Filters shelf
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.
Right-click [Product Container] in the data pane and select Create > Set…
Rename the set something like "Product Container Set" and click OK
Create a new worksheet, named "filter worksheet"
Drag [Product Container] to the Rows as well as to Colour on the Marks card
Drag [Product Container] to the Filters shelf
In the Filter dialog, check only the options that the end user should be able to see
Create a dashboard and drag both worksheets onto the dashboard
Navigate to Dashboard > Actions…
In the Action dialog select Add Action > Change Set Values…
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
Click OK twice to close all dialogs
Navigate back to the "worksheet to be filtered" worksheet
Select Analysis > Create Calculated Field
In the Calculated Field dialogue 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.
Drag [Product Container Filter (set)] to the Filters shelf
In the Filter dialogue, tick True and click OK

Excluding Values from a Filter

Question

Environment

Answer

Option 1: Use a Set as a Filter

Option 2: Exclusion Filter Using Only Relevant Values

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

Option 4: Use a Set Action

Additional information

Related links

Trending Articles

Attachments

All support

SUPPORT BY PRODUCT

TOOLS & DOWNLOADS

INFORMATION

ASSISTANCE