KNOWLEDGE BASE

Customizing a Calculation When "All" is Selected in a Filter


Published: 19 Sep 2017
Last Modified Date: 23 Oct 2017

Question

How to create a calculated field that returns a different value when "All" is selected in a filter.

Environment

Tableau Desktop

Answer

Option 1: Use the FIXED LOD expression

Create a calculated field with a name like "All selected?" with a calculation similar to the following:

WINDOW_SUM( COUNTD( [Category] ) ) = SUM( { FIXED : COUNTD( [Category] ) } )

 

More information about the above calculation, see the details below:

  • The above calculation will return True when "All" is selected in the [Category] filter. [Category] is dimension specific to the Superstore Sample data source. 
  • The WINDOW_SUM(COUNTD()) expression counts the number of values of [Category] in the view.
  • The FIXED expression counts the total number of values of [Category] in the entire data set.
  • When no dimension is included after FIXED then the expression will be evaluated over the whole data set.

Option 2: Use Parameters

  1. In the data pane, right-click [Category] and select Create Parameter.
  2. 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 "Category Parameter".
    2. Add "All" to the List of Values.
    3. Right-click [Category Parameter] in the data pane and select Show Parameter Control.
  3. Create a calculated field with a name similar to "Category Parameter Filter" with a calculation similar to the following:
    [Category] = [Category Parameter]
    OR
    [Category Parameter] = "All"
    
  4. Drag [Category Parameter Filter] to the Filters shelf. 
  5. In the Filter dialog, check True and click OK.
  6. Create a calculated field with a name like "All Selected (parameter)" with a calculation similar to the following:
    [Category Parameter] = "All"
    
Did this article resolve the issue?