How to Use Dynamic Parameters in Tableau

Automatically Update Parameter Values and Set Workbooks to Latest Date

Learn how to change fixed parameter values to dynamic so they automatically update as your data refreshes. You’ll also see how to automatically update the current value of a parameter to the latest date in the dataset.

Hi, this is Ryan with Playfair Data TV. And in this video, I’m going to show you how to use dynamic parameters in Tableau. I’ll be showing you two different use cases, including how to dynamically populate a list of allowable values and how to automatically update a parameter value to the latest date in a data set.

For the first example, we’re going to build onto one of my favorite techniques, which is highlighting a dimension member in Tableau. That’s what you see here over here in Tableau Desktop. I show you how to do this on another video. But essentially, there’s a couple of short steps to it.

I’ve got something called a Sub-Category Parameter that lists all the allowable values from the Sub-Category dimension in the Sample Superstore data set. There are 17 sub-categories out of the box in that data set.

I then have a field called Highlight that’s just saying Sub-Category equals whatever value is selected in that Sub-Category Parameter. After showing the parameter control, the user can decide what is being highlighted on the view. If they click Appliances, the red dot moves to highlight Appliances. If they click Furnishings, it moves again to highlight Furnishings, and so on.

I love that technique; I always describe it as kind of like a good map showing you where you are. You are here and it’s kind of highlighting the dimension member of focus with the other dimension members. But what happens if down the road our company starts selling something new?

In the past in Tableau, these parameters were static and their allowable values were also static. So if we started selling widgets all of a sudden, I would have to edit this parameter, go remember to add it to my list of allowable values in order for it to be available. Well, that’s where dynamic parameters come in. This is a long awaited feature that is new as of Tableau 2020.1. Now I can dynamically populate this list of allowable values using these two radio buttons over here on the right.

So this is the first example. We’re going to dynamically change this list. By default, and up to version 2020.1, these values were always fixed. Now if I choose the second radio button, I can dynamically populate the list based on the values in that field when the workbook opens.

So when the workbook opens, I want Tableau to look at the Sub-Category dimension. I will click OK. We won’t see anything change yet. We haven’t refreshed the data set. Nothing has changed. But now, instead of this list of 17 things being static, it’s going to pick up the new values if I add something new.

To illustrate that, I’m going to save this file and open the Sample Superstore data set that comes with every installation of Tableau. And I’m just going to change a couple of these entries and pretend that our company has new things to sell. Maybe I’ll update this row and say that we’re now selling widgets. And I’ll update this row and say that we’re now selling gadgets.

So I just updated two rows, but this would also work in the real world if I were adding incremental rows to my data set. So if all of a sudden we started selling gadgets and widgets and we started to see new data coming in, it would be in this data set and thus populate our new dynamic parameter.

I’m going to click Save. And then I will need to close Tableau temporarily. And then open it. Because remember, that dynamic parameter kicks off when the workbook opens. So I’m going to open the file again.

We will see a couple of things change. I should have pointed this out, but I now have 19 circles. I can count them so fast because in the bottom left corner of the Authoring interface, it shows me a summary of what is being used. Well I happen to know that there are 17 sub-categories in the Sample Superstore data set. Those two new ones are the ones I just added, widgets and gadgets.

I could further explore that to confirm by right clicking on the Sub-Category dimension and clicking Describe. Now notice that the domain is 19 members instead of 17. And if I scroll through this list, I see widgets and I see gadgets.

But here’s the really nice addition with this relatively new feature called dynamic parameters. If I edit that Sub-Category Parameter, I should see my two new rows appear and I do. Gadgets there and Widgets is here. Those were also automatically added to my parameter control.

So now the user– not only does gadgets and widgets show up here as its own circle, they can also highlight it. And that was all done dynamically without me having to remember to go back in and repopulate my list of allowable values. There’s Gadgets. And if I click Widgets, and those should correspond with the values that I updated. Widgets with 1,706 and Gadgets was something like 3,000, so I know that it’s working. So that’s the first use case.

The second use case is to dynamically pick up the latest date in a data set. I know that this was a big pet peeve for a lot of people for a long time. You couldn’t force a parameter control to automatically move to the latest data as it was being updated. But now you can.

For this example, we’re going to update this line graph that has five months of data on it. And I’m currently recording this video at the end of May 2020. I’ve also got this very simple data set that just has my five months as a dimension and then just some random fake values that I typed as one measure to get something to show up on the view.

I’m going to go back over here to Tableau Desktop. And I’ve got this little highlight working. I’m using a dual-axis combination chart to get that to show up. But just like with the first example that I shared, the user can come in here and decide what month they want to highlight.

So notice by default, we’ve got five months. And just like with our first use case up to Tableau 2020.1 and this dynamic parameters feature that I’m showing you, these allowable values were static. Not only are the values within the month parameter stuck that way until I manually go in and type something new, but they also couldn’t dynamically move to, say, if I added June data, I couldn’t get the allowable value to automatically move to June. Well, now you can. That’s what I’m going to show you next.

The biggest key to getting this to work involves updating this option right here, value when workbook opens. There’s one little trick involved that you’ll have to remember in order to get this to work. So let’s start from the beginning, actually. I noticed I had changed this radio button to something different.

But just to recap the steps that are involved to get where we are now, by default, these allowable values in a parameter are fixed. Up to version Tableau 2020.1 that was the only choice. As of version 2020.1 and beyond, there’s the option to have this list of allowable values dynamically populate if I were to choose this second radio button and choose the appropriate field. That is the use case I showed you when we dynamically populated the list of sub-categories.

For this one, there’s one extra step. If we want to also change this allowable value to always go to the latest month in the data set, we need to update this line right here that currently says Current Value. When I click that button, there is only one choice right now. It’s because we need to make a calculated field that tells Tableau what the latest month in the data set is.

And there is one little nuance to this. So that’s what I’m going to show you next. I’m first going to close this. We’re not quite done. So far all this will do is pick up new months as they get added, but they will not change this value in the parameter control to the latest month.

So I’m going to click OK. And we need to make a calculated field that computes the maximum month in the data set. Your first instinct to do that would probably be to just right-click on the Month field, hover over Create, click Calculated Field. I’ll call it Max Month. And you’d probably type MAX Month, which works for some things, but it will not work with dynamic parameters. Because this Max Month is going to be dependent on the visualization’s level of detail. So it will change as the granularity of a view changes.

To get this to work with dynamic parameters, we need to fix the maximum month at the data set level. And in order to do that, we can use level of detail expressions. For this particular use case, it is a fixed level of detail expression. There’s also two ways to type this. You can type open curly bracket, FIXED, colon, then MAX Month, then close curly bracket.

But because we’re fixing it at the highest level without addressing any specific dimension, we actually don’t need to type FIXED. So to make this formula even more elegant, you could go with that version, open curly bracket, MAX Month, close curly bracket. That will get you the exact same thing.

Now if I click OK and go edit my Month Parameter, we should see an option in this dropdown. Instead of Current Value, we’ve now got Max Month. I will go ahead and select that and click OK.

Note that it jumped to Max Month as the current value. So it went from March to May. I’m going to go ahead and save this workbook again and close it again. And I’m going to add June in here. So June 1, 2020. Let’s pretend that we refreshed the data again. And let’s say that we had a great month. We jumped up to a value of 3,000.

I’m going to save this. Open Tableau again. And one more reminder on why I’m closing it and opening it. Those dynamic parameters kick off when the workbook opens. So I’m having to close it. I’m pretending that I’m running some kind of ETL process to refresh my data set. New rows are coming in with new dates in them.

So I’ve gone ahead and run the process. Here’s the data set. Now if I open the workbook again, and if all goes well, we will see several changes appear here. We should see June appear in the data set and we do. That’s June 1st, my value of 3,000. We see that my list of parameter allowable values dynamically updated to include June.

And because we updated this row to compute or automatically set the allowable value to the highest month in the data set, it went ahead and grabbed June, put it into the calculated field for my highlight, and it dynamically moved that red mark to the latest date in the data set.

This has been Ryan with Playfair Data TV – thanks for watching!