This is amazingly difficult. Tableau's filters are "and" operations; marks appear if they match filter 1 and filter 2. Tableau has no "or" filters; you can't make marks appear if they match filter 1 or filter 2, which is what you would need to make this work on a single sheet.
What you can do to make this work, if you have Tableau 2018.3, is use another sheet as the filter control. Tableau 2018.3 introduces Set Actions, which allow you to assign the values of a set based on selection. Unlike filters, sets support "or" operations. You can create a Set 3 which contains all the values in Set 1 or Set 2. Set 3 is also known as a union of Set 1 and Set 2.
Here's how you do that.
- Create a sheet, let's call it Filter Sheet.
- Drag the field you want to filter to Rows.
- Drag the field you want to filter to Filter, and exclude "x1".
- Create a Set from the filter field (I called the field "Category") called Selected Categories. It doesn't matter which values are in the set right now.
- Create another Set from Category called Always Selected Categories, and add "x1" to the set.
- Select the "Selected Categories" and "Always Selected Categories" sets, right click, and select "Create Combined Set...".
- Make sure the "All members in both sets" option is selected. I named my combined set "Category Filter".
- Create or modify any sheets you want this filter to apply to:
- Drag the Category Filter set to the Filters shelf.
- Right-click on it in the Filters shelf, select "Show In/Out of Set", and then select "In".
- Create a dashboard.
- Add the Filter Sheet to it, as well as the sheets you applied the set filter to.
- In the menu bar, select Dashboard > Actions, click "Add Action >", and choose "Change Set Values..."
- Select "Filter Sheet" as the only Source Sheet, change the action to run on "Select", set the target set to Selected Categories, and select "Remove all values from set" when clearing the selection.
You should end up with something like this, where selecting the rows from the Filter Sheet includes those categories in the other sheet(s), but "x1" is always included.
And then you can format the filter sheet and the dashboard as desired.
It's a long workaround, but I hope it helps! I've attached a workbook demonstrating the end result.
SetActionAsSubsetFilter.twbx 14.1 KB
Thank you for your reply I really appreciate it. Unfortunately, i do not have the 2018.3. Once I get the upgrade I will work out your example.