This article shows two different ways to add All to a parameter in Tableau.
It is surprisingly simple to add All to a parameter and the All option makes parameters more useful.
The two ways in Tableau to add all to parameter are:
- Using a Condition formula in a Filter
- Using a calculated field
The starting point for both of these “all to parameter” techniques is the same.
We will use this same parameter in both examples.
The examples are using the Sample – Superstore data set that Tableau provides. We’re building a parameter filter on the Segment field.
First create a parameter and add the values into a list along with the value All.
Add All to a Tableau parameter using a Filter formula
Our parameter is filtering the Segment field. Therefore, put the [Segment] field on the filters shelf.
Edit the [Segment] filter; select Condition and By formula.
Enter the following formula:
IFNULL([Segment],"1")=IF [SegmentParameter]!="All" THEN [SegmentParameter] ELSE IFNULL([Segment],"1") END
That’s it, you have included an All in your Tableau parameter!
Add All To a Tableau parameter using a calculated field
Another option to add all is to use a calculated field as a filter instead of a formula within a filter.
First, create the calculated field. A simple boolean field is good, returning True or False. It will return True either when All is selected in the parameter OR when the parameter value is found in the dimension values.
[FilterParameterAll]: [SegmentParameter] = 'All' OR [SegmentParameter] = [Segment]
Next add the calculated field to the Filters shelf and set to True.
Now the All is now fully operational in the parameter!
Both of these examples are on Tableau Public for you to download. Plus it is embedded below.
How do these All in parameter formulas work?
Both formulas effectively do the same thing, only with slightly different syntax.
To understand what is happening it’s useful to understand the boolean data type.
What happens when All is in the Tableau parameter?
When the users choose All in a the parameter, both formulas return True.
In one it returns “1”=”1″, the other returns “All”=”All”. Both of the evaluate to True.
This doesn’t work with Null=Null, which is why we use IFNULL to convert the null values to something else.
The actual word is irrelevant – as long as both sides are the same it will evaluate to True.
This effectively cancels out the filter and it returns everything – i.e. All.
What happens when the user selects a value in the parameter that isn’t All?
Both formula evaluate to [SegmentParameter]=[Segment].
This means when the Segment chosen in the parameter is the same as the segment in the field, it returns True and returns that Segment.
For example, in the calculated field, when selecting Consumer the calculation becomes:
(1) ‘Consumer’ = ‘All’ OR (2) ‘Consumer’ = [Segment]
Ignore the first part, it returns False. Obviously the word ‘Consumer’ isn’t the same as the word ‘All’.
Which leaves Consumer = [Segment], which evaluates to True for the Consumer Segment only.
In the filter formula, which is:
IFNULL([Segment],"1") = IF [SegmentParameter] != "All" THEN [SegmentParameter] ELSE IFNULL([Segment],"1") END
this does the following:
IFNULL([Segment],”1″) = [Segment] (Segment isn’t Null, so it returns the chosen Segment)
[SegmentParameter] != “All” – this is True, Segment Parameter = Consumer so it’s not All.
Therefore, the formula evaluates the same:
[Segment] = [SegmentParameter]
Or, to put another way:
It took me a while to get my head around how this was working. Becoming more familiar with boolean values certainly helps.