KNOWLEDGE BASE

Creating a Condition that Returns Different Results When "All" is Selected in a Filter


Published: 19 Sep 2017
Last Modified Date: 25 Jun 2020

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:

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

  • The above calculation will return True when "All" is selected in the [Category] filter. ([Category] is a dimension specific to the Superstore Sample data source. )
  • The TOTAL(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"
    

Additional Information

Option 1 may require adjusting the calculation or Compute Using settings every time dimensions are added or removed from the view.

If Option 1 is not returning the expected results, it may be necessary to change how the table calculation is computed. For more information, see Transform Values with Table Calculations.


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