3 Ways to Add “All” to a Tableau Dynamic Parameter

In version 2020.1, Tableau introduced a much-anticipated feature, dynamic parameters, which allows us to automatically populate a parameter’s allowed values based on data as well as updating the current value based on some condition. As Kevin discussed on his blog, The Key to Dynamic Parameters & Some Good Use Cases, this feature was years in the making and was the most upvoted feature request on the Tableau Community Forums.

 

This has been an amazing feature that has solved so many problems. However, there is one common request I continue to see from Tableau users. Take, for example, this parameter from Superstore, which is automatically populated based on the Region:

 

 

This works perfectly, but often people may wish to add another value to the list—most commonly “All”. Unfortunately, there is no straightforward way to do this. Once you select the option to dynamically update the parameter when the workbook opens, you cannot manually add any new items to the list. So, in order to add “All” (or some other item) to the parameter, that value needs to be part of your data set. Fortunately, there are a few different techniques you can use to do this and, in this blog, I’ll be sharing 3 of these methods.

 

Method 1: Duplicate Your Data

This first method is a bit of a hack. It includes two steps. The first step is duplicating your data. There are two ways we can do this—union or join. Let’s start with a union. Since Region comes from the Orders table, we’ll need to union Orders to itself.

 

 

This will, of course, duplicate our data; it will also create a new field called Table Name, which contains the name of our two unioned tables—in our case, Orders and Orders1. We’ll use this in our second step…

 

 

The second way to duplicate our data is using a join. We can create a simple table in Excel or csv format that looks something like this:

 

 

Then we’ll relate (or join) this to our Orders table. We’ll relate/join based on a calculation with the value “1”. By relating/joining the tables in this way, we’ll duplicate each record in the Orders—one with Type = “Region” and one with Type = “All”

 

 

Now that our data is duplicated, we’ll move onto the second step of this technique. We’ll use either Table Name (if duplicated using a union) or Type (if duplicated using a join) to create a dummy “All” value for the Region. To do this, we’ll create a calculated field called Region with All:

 

IF [Table Name]="Orders" THEN

    [Region]

ELSE

    "All"

END

 

or….

 

IF [Type]="Region" THEN

    [Region]

ELSE

    "All"

END

 

The first Orders table will give us a lists of the regions and the second will always return “All”, resulting in a list of regions plus “All”.

 

We now use this to populate the dynamic parameter:

 

 

If you use this method, you need to remember to filter all of your views so that you’re only showing one of the Orders tables. You can do this by filtering on Table Name/Type and applying that filter to all sheets using the data source. These filters will not impact the updating of the dynamic parameter.

 

Method 2: Custom SQL

If you’re using a SQL-compliant database, then you can write custom SQL to create an artificial “All” value without duplicating your data. Take, for example, this data model which is connected to my publicly-available SQL Server database (for details on this database and how you can use it yourself, see SQL for Tableau Users Part 1: The Basics).

 

 

This data model, as is, will only include the four regions—Central, East, South, and West. But, we can artificially add “All” by converting the Orders table to use custom SQL. This will result in SQL that looks like this:

 

SELECT

  [Orders].[Row ID] AS [Row ID],

  [Orders].[Order ID] AS [Order ID],

  [Orders].[Order Date] AS [Order Date],

  [Orders].[Ship Date] AS [Ship Date],

  [Orders].[Ship Mode] AS [Ship Mode],

  [Orders].[Customer ID] AS [Customer ID],

  [Orders].[Customer Name] AS [Customer Name],

  [Orders].[Segment] AS [Segment],

  [Orders].[Country] AS [Country],

  [Orders].[City] AS [City],

  [Orders].[State] AS [State],

  [Orders].[Postal Code] AS [Postal Code],

  [Orders].[Region] AS [Region],

  [Orders].[Product ID] AS [Product ID],

  [Orders].[Category] AS [Category],

  [Orders].[Sub-Category] AS [Sub-Category],

  [Orders].[Product Name] AS [Product Name],

  [Orders].[Sales] AS [Sales],

  [Orders].[Quantity] AS [Quantity],

  [Orders].[Discount] AS [Discount],

  [Orders].[Profit] AS [Profit]

FROM [dbo].[Orders] [Orders]

 

To add the “All” option, we need to add a union that will return “All” for the region. As detailed in my blog on combining data using SQL, two tables or SQL statements must have the same set of fields in order to union them together, so we can just provide a value of NULL for every field except Region as shown here:

 

SELECT TOP 1

  NULL AS [Row ID],

  NULL AS [Order ID],

  NULL AS [Order Date],

  NULL AS [Ship Date],

  NULL AS [Ship Mode],

  NULL AS [Customer ID],

  NULL AS [Customer Name],

  NULL AS [Segment],

  NULL AS [Country],

  NULL AS [City],

  NULL AS [State],

  NULL AS [Postal Code],

  'All' AS [Region],

  NULL AS [Product ID],

  NULL AS [Category],

  NULL AS [Sub-Category],

  NULL AS [Product Name],

  NULL AS [Sales],

  NULL AS [Quantity],

  NULL AS [Discount],

  NULL AS [Profit]

FROM [dbo].[Orders] [Orders]

 

We only need a single record with the “All” region, so I’ve add TOP 1 clause to the SELECT statement so that it only returns a single record. Note: This syntax is different on other platforms. See this w3schools article for further details.

 

 

Then we can use a UNION ALL to combine these into a single data set.

 

SELECT

  [Orders].[Row ID] AS [Row ID],

  [Orders].[Order ID] AS [Order ID],

  [Orders].[Order Date] AS [Order Date],

  [Orders].[Ship Date] AS [Ship Date],

  [Orders].[Ship Mode] AS [Ship Mode],

  [Orders].[Customer ID] AS [Customer ID],

  [Orders].[Customer Name] AS [Customer Name],

  [Orders].[Segment] AS [Segment],

  [Orders].[Country] AS [Country],

  [Orders].[City] AS [City],

  [Orders].[State] AS [State],

  [Orders].[Postal Code] AS [Postal Code],

  [Orders].[Region] AS [Region],

  [Orders].[Product ID] AS [Product ID],

  [Orders].[Category] AS [Category],

  [Orders].[Sub-Category] AS [Sub-Category],

  [Orders].[Product Name] AS [Product Name],

  [Orders].[Sales] AS [Sales],

  [Orders].[Quantity] AS [Quantity],

  [Orders].[Discount] AS [Discount],

  [Orders].[Profit] AS [Profit]

FROM [dbo].[Orders] [Orders]

UNION ALL

SELECT TOP 1

  NULL AS [Row ID],

  NULL AS [Order ID],

  NULL AS [Order Date],

  NULL AS [Ship Date],

  NULL AS [Ship Mode],

  NULL AS [Customer ID],

  NULL AS [Customer Name],

  NULL AS [Segment],

  NULL AS [Country],

  NULL AS [City],

  NULL AS [State],

  NULL AS [Postal Code],

  'All' AS [Region],

  NULL AS [Product ID],

  NULL AS [Category],

  NULL AS [Sub-Category],

  NULL AS [Product Name],

  NULL AS [Sales],

  NULL AS [Quantity],

  NULL AS [Discount],

  NULL AS [Profit]

FROM [dbo].[Orders] [Orders]

 

Now, when we feed our Region into the dynamic parameter, the “All” value will be included.

 

 

Unfortunately, this technique is a bit of pain—we have to convert a table to custom SQL, then build all these NULL values into our SQL. To avoid this, I tend to use a slightly different approach. Instead of modifying the primary data source, I prefer to create an entirely separate data source that is solely for the purpose of populating the dynamic parameter.

 

We can start out with a simple SELECT DISTINCT query for our regions:

 

SELECT DISTINCT [Region] FROM [Orders]

 

Then we can add our UNION ALL and a secondary query to get “All”:

 

SELECT DISTINCT [Region] FROM [Orders]

UNION ALL

SELECT TOP 1 'All' as [Region] FROM [Orders]

 

This will return 5 rows:

 

 

We then use this to populate the dynamic parameter.

 

 

By creating a separate data source in Tableau, we can populate the dynamic parameter as desired without the need to make any changes to our original data source.

 

Method 3: Use an Extension

The third and final method comes from Tableau Forums Ambassador Diego Martinez. Diego works at Modux, a Tableau Partner in Colombia. If you're looking for some great Spanish-language webinars and blogs, check out the content on their site (https://www.modux.co/webinars-tableau and https://www.modux.co/articulos-tableau)

On a forums post, Diego recommended using Data-Driven Parameters extension to deal with this problem. Before configuring the extension, you’ll need to change the parameter to allow all values:

 

 

You’ll also need to create a dashboard an add a sheet using your primary data source. I have something like this:

 

 

Note: The parameter does not need to be visible, but I’ve added it so that we can 1) See the value changing as we use the dynamic parameter and 2) show that it’s now a type-in parameter that allows any value.

 

Now we add the extension to the dashboard and configure it as follows.

 

 

On the Options tab, we’ll select the option for including “All”:

 

 

Note: This option defaults the text to “(All)” but you can change it to any value you prefer. I’ve changed it to simply “All”.

 

The data-driven parameter will now include all of the regions as well as “All”.

 

 

When you select a value from the parameter, it will automatically update the original Region Parameter to the selected value (see the “All” value in the above image). To ensure that users do not manually change the value, be sure to remove this parameter from the dashboard—you only want them to interact with the data-driven parameter.

 

Wrap-Up

So, there you have it—3 ways to add “All” (or some other value) to a dynamic parameter. Each of these has its advantages and disadvantages, of course. The data duplication option causes you to double the size of your data, which is never ideal. The custom SQL option is quite nice—particularly if you use a separate data source for populating the parameter—but it creates an additional data source you need to be maintain and is not an option for non-SQL data sources. The extension is quite easy to implement, but it requires enabling the use of the extension in your environment, which may or may not be an option for some users. Ultimately, which of these is best for you is dependent upon your use case and environment, but I’m hopeful that at least one of these will be an option when you need it.

 

Thanks for reading!!

 

Ken Flerlage, August 16, 2021

Twitter | LinkedIn | GitHub | Tableau Public