Dynamic parameters were introduced with Tableau Desktop version 2020.1 and rightfully created a lot of buzz among Tableau authors due to their automation capabilities. Prior to dynamic parameters, parameters were static, meaning that if you had a parameter with a data type of Date, the date would be stuck until a user changed it. This was not an ideal user experience because you couldn’t automatically update a dashboard to the latest date in a dataset when using parameters.
With dynamic parameters, we can automatically change a parameter value each time the workbook is loaded – and they’re not just useful for dates! In this post, I will show you three must-know ways to use dynamic parameters, including how to dynamically populate a list of allowable string values, how to dynamically update a date parameter to the latest date, and how to update a list of allowable numerical values.
Related video tutorial:
Premier Tableau eLearning from Playfair Data TV
Dynamically Populate a List of Allowable Values
To start us off I have connected to the Sample-Superstore dataset and built one of my favorite techniques; which is highlighting a dimension member in Tableau on a minimalist dot plot.
In this view I have highlighted a dimension member based on the selection in the parameter and added a reference line for average Sales. This allows us to choose a Sub-Category and see how it is performing compared to the other sub-categories.
So if I change the Sub-Category Parameter to Art, it will highlight the Art Sub-Category.
✎ Related post: An Introduction to Parameters in Tableau
Let’s take a look at the parameter by right-clicking on the Sub-Category Parameter in the Data pane and selecting Edit.
We can see that this parameter has a String data type and a list of allowable values. We can also see in the bottom right of the dialog box there is a radio button that is selected called “Fixed”. This means that the values in the list are fixed and will not update dynamically. I also want to note as of Tableau Desktop 2020.3, the version I am using to author this blog, this is the default selection.
Now to illustrate the value of dynamic parameters, let’s imagine for a moment that this view is in Tableau Server and the Sample Superstore introduced two new sub-categories: Widgets and Gadgets. With this parameter being built as a list of fixed string values, the author of this workbook would need to update the list with the two new sub-categories, then republish this workbook into Tableau Server for them to show up within the list of allowable parameter values. Times that by ten workbooks… or 50! Now you can really start to see the value that dynamic parameters can have. They make our workbooks less error prone and more consistent.
To make this parameter dynamic we are going to select the second radio button next to the list of allowable values called “When workbook opens”. A dropdown will appear and from that dropdown we will select Sub-Category, then click OK.
This is saying that each time the workbook is opened, Tableau will look for any new sub-categories and if one is found it will be added to the list of allowable values.
We have just made this parameter dynamic. Easy, right?!
Now let’s test it out. I am going to close the Tableau workbook and open the Sample – Superstore dataset in Excel.
In the Sub-Category column, I am going to change a value to Widgets and another value in that column to Gadgets. This is going to simulate that two new sub-categories were created in our dataset.
I will save that file, close it, and reopen Tableau. If everything worked correctly, we should now see Gadgets and Widgets in our list of allowable values.
I highlighted Gadgets and Widgets is at the bottom of the list. This worked perfectly and will now dynamically update even if additional sub-categories are introduced.
How to Dynamically Change a Date Parameter to the Latest Date
For this example, I have created my own dataset with values from 1/1/2020 through 10/1/2020. I have plotted the data on a dual-axis combination chart.
✎ Related post: 3 Ways to Use Dual-Axis Combination Charts in Tableau
This line graph will highlight the selected date on the graph using the date parameter. What I want this to do is dynamically update to the latest date each month new data comes in. For instance, in November when the data refreshes, I want November 2020 to be highlighted.
To do this we will setup the parameter in a similar way as the previous example. However, not only do we want Tableau to load in the new values, we also want Tableau to select that new value once the workbook loads. To accomplish that we need to create a calculated field that will be used to pass Tableau the value to select.
First, we will right-click any blank space in the Data pane and select “Create Calculated Field”. I will title my new field “Max Date” and the calculation will be as follows:
This will return the FIXED MAX date in the dataset.
✎ Related post: Introduction to Level of Detail Expressions
I will click OK.
Next, I will right-click on my Month Parameter in the Data pane and select “Edit”.
Just like before in the lower-right corner, I will select the “When workbook opens” radio button and choose my Date field from my dataset. However; I am also going to click on the dropdown next to “Value when workbook opens”. This is the third dropdown from the top in the Edit Parameter dialog. In this menu I will select our new Max Date calculated field.
Then I will click OK.
Once again, I am going to close Tableau and open my dataset in Excel.
As I mentioned before, I currently have values from 1/1/2020 through 10/1/2020.
I am going to create an entry for 11/1/2020 and add a new value in it. This is going to simulate as if we are in Nov 2020 and my data just refreshed.
Now I will save that, close Excel, and reopen my Tableau workbook.
When I did you can see that the parameter dynamically loaded in the latest data, but also moved the highlighted date to the latest month in the dataset!
This is just one way to use dynamic date parameters. You can also use this feature when using the popular Start and End Date parameter setup. If you made both the start date and end date parameters dynamic, they would automatically update to a specific date or date range each time the workbook is open.
How to Dynamically Update List of Allowable Numerical Values Using Benchmarks
To begin I will create a new sheet in Tableau and drag sum of Profit to the Rows Shelf and continuous Month of Order Date to the Columns Shelf. This gives me a line graph of profit by month.
For our benchmarks, I have again created a new data source in Excel and loaded some values from three of our competitors.
I will connect to my new data source by clicking Data in the top navigation and selecting “New Data Source”. I will select Excel as my connection type and click on my new data source.
With the data source added, I will create a new parameter for my benchmarks. I will right-click in a blank space in the Data pane and select “Create Parameter”. I will title the parameter Competitor Benchmarks, click the List radio button, then click the “When workbook opens” radio button. Next, I will simply select Value from my benchmark dataset and the values will preload into the list. This can be a nice shortcut if you are dealing with loading a lot of values in a list initially because you don’t have to type them into the list.
To get these benchmarks to display on the line graph I created, I am going to right-click on the y-axis and select “Add Reference Line”.
In the dialog box, I will select Competitor Benchmarks in the “Value” dropdown, change the line color to red, and click OK.
Now that we have the benchmark parameter setup, I will close Tableau, navigate back to my benchmark data source, and add a fourth competitor.
I’ll save that file and reopen Tableau.
As expected, Tableau dynamically updated the parameter with the new value.
Until next time,
Senior Manager, Analytics Engineering