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:
Consumer=Consumer
It took me a while to get my head around how this was working. Becoming more familiar with boolean values certainly helps.
Hi, I want to do the same but I want to pass value ‘All’ as url parameter. like this >>>>> ?segment=All attached at end of url on server. But this displays blank dashboard. Can you please help how to make that work?
Thanks.
Ok, you’re asking about URL parameters rather than Tableau parameters. Check this article on passing parameters in the URL. The only way to do what you want (assuming it’s the All value in a filter) is to pass all of the possible filter values into the URL. This isn’t practical for some fields as there are too many values. Another option is to remove the filter from the URL parameters entirely, which by default will select all values.
Hi Andrew,
Do you know of any way to add “All” value to a dynamic parameter? I want my parameter list to be updated every time a file is being used, but whenever I switch the list from “fixed” to “when the workbook opens”, the type-in option disappears.
I really appreciate any help you can provide.
Great question. Are you able to union in a new record into the data source, only containing the word “all” in one field?
Didnt work for me
Can you be more specific? Both methods work, interested to know where you think it falls over. FYI, I noticed the embedded workbook was no longer embedded – some change to Tableau Public I think – it’s now there so you can download the workbook and deconstruct what it’s doing.
This is quality content, thanks for sharing!
Me having worked with Power BI before still lack quality developer content for Tableau. Keep up the good work!
Adam
Hi Andrew, I have created a parameter from one of the Group Dimension here I am unable to insert all option it is giving me blank sheet
secondly I have created a parameter from copying it from clipboard customised one , herealso I am unable to insert all option .
Could you pls suggest the option how to achieve this ?
You’re struggling the get the “All” text into the parameter? Is that the issue?