KNOWLEDGE BASE

 Including Filters in Calculations Without Including them on the Filter Card


Published: 30 Nov 2015
Last Modified Date: 25 Jun 2020

Question

How to filter one or more fields without filtering the entire view.

Environment

Tableau Desktop

Answer

Create a logic calculation by using an IF / THEN statement to return a measure for only certain dimension values. The dimension value could be hard-coded or could be dynamically defined with a parameter or set.

The directions below start from the worksheet "Original" in the workbook "", which is downloadable from the right-hand pane of this article. Directions for creating the worksheet "Original" and demonstrations of all of the variations are also included in the workbook.

Variation 1: Hard-coding a dimension value

  1. Select Analysis > Create Calculated Field
  2. 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 "Sales Label (variation 1)"
    2. In the formula field, create a calculation similar to the following:

      IF DATEPART('year', [Order Date]) = 2019
      THEN [Sales]
      END

      The above calculation will return the measure [Sales] if the [Order Date] is in the year 2019. Otherwise the calculation will return NULL. An "ELSE 0" could be added before the "END" to return zero instead of NULL.

      Other conditions could be used such as [Order Date] = #1/1/2019# to filter to a specifc date, or [Segment] = "Consumer" to filter to a specifc text value. The important part is to make sure the value after the equal sign is the same type of data as the dimension.
  3. Replace [Sales] on Label with [Sales Label (variation 1)]
 

Variation 2: Use parameters

  1. Select Analysis > Create Calculated Field
  2. 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 "Year of Order Date"
    2. In the formula field, create a calculation similar to the following:

      DATEPART('year', [Order Date])

      Steps 1 & 2 create a field that contains only the year date part of [Order Date] as an integer value. The [Year of Order Date] field makes it easier to create a parameter, but otherwise is not necessary
  3. In the data pane, right-click the [Year of Order Date] field and select Create > Parameter…
  4. In the Create Parameter dialog box do the following and click OK
    1. In the Name text box, type a name. In this example I will call the parameter "Select a year"
    2. Under Properties, in the Data type list, select integer
    3. Under Allowable values, select List
    4. In the Display Format dropdown, select Number (custom) and set the format to 0 decimal places and uncheck include thousands separators
    • Starting in Tableau Desktop 2020.1 parameters can pull list values from a field in the data source by selecting a field in the When workbook opens dropdown.
  5. Right-click [Select a year] in the data pane and select Show Parameter Control
  6. Create a calculated field with a name like "Sales Label (variation 2)" with a calculation similar to the following:

    IF DATEPART('year', [Order Date]) = [Parameters].[Select a year]
    THEN [Sales]
    END

    Note: The syntax "[Parameters]." is automatically added when a parameter has the same name as another field in the data source.
  7. Replace [Sales] on Label with [Sales Label (variation 2)]

Variation 3: Use set controls

Starting in Tableau Desktop 2020.2, set controls can be shown to allow end users to change the values in sets.
  1. Select Analysis > Create Calculated Field
  2. 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 "Year of Order Date"
    2. In the formula field, create a calculation similar to the following:

      DATEPART('year', [Order Date])
  3. Right-click [Year of Order Date] and select Convert to Dimension

    Steps 1-3 create a dimension that contains only the year date part of [Order Date] as an integer value. The [Year of Order Date] field is required to make a set of years because sets must be built off of fields in the data source.
  4. In the data pane, right-click the [Year of Order Date] field and select Create > Set…
  5. Give the set a name and click OK. In this example, the set is named "Select a year"
  6. Create a calculated field with a name like "Sales Label (variation 3)" with a calculation similar to the following:

    IF [Select a year]
    THEN [Sales]
    END

    The above calculation will return the measure [Sales] if the [Order Date] is in a user selected year. Otherwise the calculation will return NULL. An "ELSE 0" could be added before the "END" to return zero instead of NULL.

    Sets are Boolean fields that return either TRUE or FALSE. Therefore the set by itself is the entire condition.
  7. Replace [Sales] on Label with [Sales Label (variation 3)]
  8. Right-click the set [Select a year] in the data pane and check Show Set

Additional Information

Parameters versus Sets

  • Parameters can only hold a single value, whereas set controls allow for multi-select.
  • Parameters can hold any arbitrary values, whereas sets are always tied to a field in the data source. Although those field values can be re-aliased.
  • Parameters can be used to filter across data sources that have no relationship. See Filtering Across Multiple Data Sources Using a Parameter Sets can only filter across data sources when there is a relationship set up between data sources. See Filter Data Across Multiple Data Sources
  • Parameters can be used in dashboard text objects or titles, whereas sets (or calculated fields using sets) can only be used in worksheets elements because sets are part of the data source.
  • Sets will always update when new data is brought into the data source. Parameters can be set up to bring in new data. See step 5 in Create Parameters


This solution can be nested inside of other calculations. For example, it might be included in level of detail (LOD) calculation like
{INCLUDE [Dimension]: SUM(IF [Dimension] = 'FilterValue' THEN [Non-Aggregated Measure] END)}



Discuss this article... Feedback Forum
Did this article resolve the issue?