Home > Tableau > Add an All to a parameter in Tableau

Add an All to a parameter in Tableau

November 12th, 2012 Leave a comment Go to comments

EDIT 3/10/16: A different and simpler way to add All to a parameter in Tableau is written in a new article.

 

I have to thank Ben Sullins for this and his post on how to add an All to a parameter in Tableau, finding that article saved me a lot of time.

To begin create the parameter and add an ‘All’ option. Display the parameter on your worksheet.

Next drag the field you want to filter to the filters shelf.

To filter the field using the parameter right click on the field on the filters shelf and select Filter, then Condition.

In the By Formula box enter the following formula:

IFNULL([FilterField],’Null’) = IF [Paramter] != ‘All’ THEN [Paramter] ELSE IFNULL([FilterField],’Null’) END

How does this formula work?

Firstly in the filter By Formula box the result must be Boolean, either True or False. In this case we want to make the left side, IFNULL([FilterField],’Null’), equal the right side, IF [Paramter] != ‘All’ THEN [Paramter] ELSE IFNULL([FilterField],’Null’) END.

By using the IFNULL in both the left side and the right side we have ensured the answer will always be True – hence it will return something and you won’t be left with a blank report.

The best way to see what is happening here is to start with the non-All values in the parameter. Alter the formula in the FilterField By Formula filter to become [FilterField] = [Parameter]. Now when you use the parameter you’ll see if works for everything selected apart from All as the All isn’t in the FilterField.

When All is selected in the paramter the formula evaluates to False because the value ‘All’ does not exist as a value in the FilterField so nothing is returned – i.e this is false as FilterField would return NULL as the value ‘All’ isn’t in the field – the formula would evalue to “NULL = All” which is false because NULL does not equal the word ‘All’.

Now edit the FilterField By Formula to become ‘Null’ = ‘Null’. This takes away the link to the parameter, which is exactly what our original formula does. Using the parameter now does nothing, the report will always show All regardless of what’s selected in the parameter.

The IFNULL([FilterField],’Null’) converts any non-existent (NULL) value and converts it to ‘Null’ in this case. You could replace the word ‘Null’ with any word, as long as the left and right side evaluate to the same value it will work – e.g. IFNULL([FilterField],’Carrot’) = IF [Paramter] != ‘All’ THEN [Paramter] ELSE IFNULL([FilterField],’Carrot’) END

That’s how it works. A very useful little trick.

Categories: Tableau Tags: , , ,
  1. Mark Russell
    March 5th, 2014 at 17:29 | #1

    This is a game changer. Being able to use Parameters as true filters is enormous. Nicely done!

  2. Priyanka
    June 20th, 2014 at 17:00 | #2

    This is great. Thanks! I am trying to first filter a data with 2-3 variables and then get all records from another data for the customers who satisfied those filters in the first data through custom sql. I included ‘ALL’ in the option while filtering in the first data, but if I select ALL in one of the variables it’s not getting any data from the second data using custom sql. Could you plz help? Thanks!

    • awatson
      June 20th, 2014 at 19:26 | #3

      Could you supply the SQL statement? That might help me understand what you’re trying to do, how you’re trying to link the data sources.

  3. Jon Foote
    April 16th, 2015 at 23:55 | #4

    Awesome, thank you. Couldn’t for the life of me figure this out, but this is a nice solution.

  4. Travis
    September 22nd, 2015 at 17:05 | #5
  5. November 12th, 2015 at 16:39 | #6

    Nice example. Thanks for posting.

  1. No trackbacks yet.