KNOWLEDGE BASE

Sheet Targeted by Multiple Action Filters Shows Less Data than Expected


Published: 16 Feb 2017
Last Modified Date: 16 Oct 2019

Issue

When multiple Filter Actions are targeting one worksheet, less data shows than is expected when you want the worksheet to be filtered by only one Filter Action at a time (aka you want one Filter Action to overwrite other Filter Action)

For example:
  • Action Filter A filters the target worksheet to show only "apples".
  • Action Filter B filters the target worksheet to show only "April", but no apples were sold during April, so the view is blank.

Environment

Tableau Desktop

Resolution

Option 1: Add all filtering fields to each source worksheet

A Filter Action uses the dimensions in the source worksheet to filter the target worksheet. If the the source worksheet contains all filtering fields, for example [Date] and [Fruit], then the source worksheet will overwrite over Filter Actions using the same filtering fields.

The example workbook "conflicting action filters.twbx" in the right-hand pane of this article uses the sample data set Superstore to demonstrate the following directions:
  1. Drag [Fruit] to Detail on the Marks card
  2. Select Analysis > Create Calculated Field
  3. 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 "Color only one mark"
    2. In the formula field, create a calculation similar to the following:

      FIRST() = 0

      The FIRST() functions numbers marks in the view starting with zero.
      Adding [Fruit] to the view created a separate mark for each fruit, but we cannot filter out or hide any marks from the view otherwise the Filter Action will only pass the fruit values still in the view.
  4. Drag [Color only one mark] to Color on the Marks card
  5. Right-click [Color only one mark] on Color and select Edit Table Calculation…
  6. In the Table Calculation dialog, do the following and close the dialog:
    1. For Compute Using, select Specific Dimensions
    2. Check only Fruit
      In this example, we only added [Fruit] to the view because that's the only dimension the other source sheet is passing in its Filter Action. If multiple dimensions were added to the view, then they should all be checked.
  7. Click Color on the Marks card and select Edit Colors…
  8. In the Edit Color dialog, do the following and click OK:
    1. Click True and then pick the desired color to show in the view
    2. Double-click False
    3. In the Select Color dialog pick white and click OK
  9. Create a calculated field with a name like "Number of Records Excluding Fruit" with a calculation similar to the following:

    { EXCLUDE [Fruit] : SUM([Number of Records])}

    The EXCLUDE function will compute SUM([Number of Records]) as if [Fruit] was removed from the view. If multiple dimensions were added in step 1, then all dimensions should be listed after EXCLUDE separated by commas.
  10. Replace [Number of Records] on Text with [Number of Records Excluding Fruit]
  11. Navigate to Analysis > Stack Marks > Off
  12. Repeat steps 1-11 for the "Sales per Fruit" with MY(Date) instead of [Fruit]. Note: when using date fields, it is important that the date level match exactly between Filter Actions, otherwise the Filter Actions will not overwrite each other.
 

Option 2: Use Set and Parameter Actions

Set Actions can be used to filter a target view similar to a Filter Action using a calculated field. Parameter Actions will allow us to know which worksheet was clicked last. Parameter Actions are a new feature in Tableau Desktop 2019.2.

The example workbook "conflicting action filters.twbx" in the right-hand pane of this article uses the sample data set Superstore to demonstrate the following directions:
  1. Right-click [Date] in the data pane and select Create > Custom Date…
  2. In the Create Custom Date dialog, for Detail select Month / Year and click OK
  3. Right-click [Date (Month / Year)] in the data pane and select Create > Set…
  4. In the Create Set dialog, name the set something like "Months Set" and click OK
  5. Repeat steps 3-4 for [Fruit] to create [Fruits Set]
  6. Click the down arrow next to Dimensions in the data pane and select Create Parameter…
  7. In the Create Parameter dialog, do the following and click OK:
    1. Name the parameter. In this example I will call it "Last Clicked View"
    2. For Data Type, choose String
    3. For Allowable values, select List
    4. Under List of values, type out the names of the filters. The end user will not see these names.
  8. Select Analysis > Create Calculated Field
  9. 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 "Is Month"
    2. In the formula field, create a calculation similar to the following:

       "month"

      This value should match the value in the [Last Clicked View] parameter
  10. Create a calculated field with a name like "Is Fruit" with a calculation similar to the following:

    "fruit"
  11. Navigate to the "Sales per month" worksheet
  12. Drag [Is Month] and [Date (Month / Year)] to Detail on the Marks card
  13. Navigate to the "Sales per Fruit" worksheet
  14. Drag [Is Fruit] to Detail on the Marks card
  15. Create a calculated field with a name like "Filter by last clicked value(s)" with a calculation similar to the following:

    ( [Last Clicked View] = "fruit"
    AND [Fruits Set] )
    OR
    ( [Last Clicked View] = "month"
    AND [Months Set] )

    Sets return either TRUE or FALSE values, and thus can be used to filter the view by themselves. When we first created the sets, we did not check any members; Thus everything is FALSE currently.

    We are going to use Set Actions to update which members are TRUE, but we only want to use one filter at a time. Therefore we will update [Last Clicked View" with a Parameter Action.
  16. Navigate to the dashboard
  17. On the dashboard, navigate to Dashboard > Actions…
  18. In the Actions dialog, do the following:
    1. Select Filter 1 (generated) and click Remove
    2. Select Filter 2 (generated) and click Remove
  19. Click Add Action > Change Parameter…
  20. In the Add Parameter Action dialog, do the following and click OK:
    1. Name the action something like "Sales per Fruit was selected"
    2. For Source Sheets, check only Sales per Fruit
    3. For Target Parameter, select Last Clicked View
    4. For Value, for Field, select Is Fruit
  21. Repeat steps 19-20 for "Sales per Month was selected" using [Is Month] instead of [Is Fruit]
  22. Click Add Action > Change Set Values…
  23. In the Add Set Action dialog, do the following and click OK:
    1. Name the Set Action something like "Add Fruit Values to Set"
    2. For Source Sheets, check only Sales per Fruit
    3. For Target Set, for the first dropdown menu, select the appropriate data source
    4. For Target Set, for the second dropdown menu, select Fruits Set
    5. For Clearing the select will, select Remove all values from set
  24. Repeat steps 22-23 for "Add Month Values to Set" using Months Set instead of Fruits Set
  25. Click Ok to close the Actions dialog
  26. Navigate to the "Details" worksheet
  27. Drag [Filter by last clicked value(s)] to the Filters shelf
  28. In the Filter dialog, check True and click OK
 

Option 3: Swap Between Source Sheets

Instead of always showing all source sheets, a parameter can be used to swap between source sheets. Then we can create one dimension that will be on all source sheets to use in one Filter Action.

The example workbook "conflicting action filters.twbx" in the right-hand pane of this article uses the sample data set Superstore to demonstrate the following directions:
  1. Click the down arrow next to Dimensions in the data pane and select Create Parameter…
  2. In the Create Parameter dialog, do the following and click OK:
    1. Name the parameter. In this example I will call it "Select a view"
    2. For Data Type, choose Integer
    3. For Allowable values, select List
    4. Under List of values, add an integer for each source worksheet and change the Display As to be the name of the worksheet. You could also use a string parameter, but integers tend to be more performant.
  3. Select Analysis > Create Calculated Field
  4. 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 "Select a View Filter"
    2. In the formula field, create a calculation similar to the following:
    3. [Select a view]
  5. Right-click [Select a View Filter] in the data pane and select Convert to Dimension
  6. Create a calculated field with a name like "Dimension for Filter Action" with a calculation similar to the following:

    CASE [Select a view]
    WHEN 1 THEN STR(DATETRUNC('month', [Date]))
    WHEN 2 THEN [Fruit]
    END
  7. Navigate to the "Sales per month" worksheet
  8. Drag [Select a View Filter] to the Filters shelf
  9. In the Filter dialog, do the following and click OK:
    1. Select Custom value list
    2. Type 1
    3. Hit the + symbol to add 1 to the list
  10. Drag [Dimension for Filter Action] to Detail on the Marks card
  11. Repeat steps 7-10 for the "Sales per Fruit" worksheet
  12. Navigate to the dashboard
  13. In the Actions dialog, do the following:
    1. Select Filter 1 (generated) and click Remove
    2. Select Filter 2 (generated) and click Remove
  14. Click Add Action > Filter…
  15. In the Add Filter Action dialog, do the following:
    1. For Source Sheets, check both Sales per Fruit and Sales per month
    2. For Run action on, choose Select
    3. For Target Sheets, check only Detail
    4. For Clearing selection will, select Show all values
    5. For Target Filters, choose Selected Fields
    6. Click Add Filter…
  16. In the Edit Filter dialog, for both Field dropdowns, select Dimension for Filter Action
  17. Click OK three times to close all dialogs
  18. Click the Show/Hide Cards icon above the dashboard that looks like a small bar graph, and Parameters > Select a View
  19. Right-click the title on "Sales per Month" worksheet on the dashboard, and select Hide Title
  20. Repeat step 19 for the "Sales per Fruit' worksheet on the dashboard
  21. Note, the source worksheets may not collapse as expected if they were not both added to the same horizontal layout container.
Did this article resolve the issue?