A workaround to display relevant values only in a parameter drop-down in Tableau

Recently I encountered a scenario in which I needed to highlight days on a calendar according to a category selection. For example, let’s say a user of a sales dashboard requested a feature that allowed them to choose from a drop-down menu of cities to highlight days on the calendar in which there were orders from that city.

This is a pretty simple feature to implement with a parameter. The problem I faced is that when showing the parameter drop-down on the dashboard, it listed all of the cities in the dataset, not just the cities relevant to the month on the calendar. If we were using a filter, we could easily use Tableau’s built-in settings to show relevant values only and the problem would be solved. Parameter selectors don’t have this same built-in functionality, and the volume of cities in the dataset was substantial and changed from month to month.

I discovered a workaround to this issue by building my own drop-down selector using dashboard actions, the show/hide containers feature, and some formatting tricks.

You can find the dashboard on my Tableau Public page.

Here’s a quick look at the difference between a traditional parameter drop-down (right) and my workaround (left).

In the upcoming explanation of this workaround, I’ve skipped the instructions for prepping the data and building the calendar itself. If you’re interested, I plan to dedicate a follow-up article to those pieces soon.

Building a Parameter Drop-Down Showing Relevant Values Only

The workaround consists of four main steps:

1. Build a sheet that will act as the drop-down menu which lists all the possible city values. Then set a filter that limits the list to only the cities relevant to the selected calendar month.
2. Add that sheet onto the dashboard in a container that uses a show/hide button. This button will create the drop-down filter effect.
3. Format the elements to look like a normal drop-down filter so it fits in with other filters/parameters you have on your dashboard.
4. Implement dashboard actions to update the calendar when the user selects a city from the new workaround drop-down menu.

Preliminary data prep

I started with Tableau’s Sample Superstore dataset and performed a full outer join on [Order Date] to an Excel file with a single column of all dates from 1/1/2019 to 12/31/2022. More details on this to come in my next article.

The [City] field from the Superstore dataset will be used as the primary field in this example. You’ll also need to build a parameter called [CITY]: Set the data type to String, set Allowable values to List, and Add values from the [City] field. Note that I name parameters in ALL CAPS and fields are named in Title Case.

The last preliminary step you’ll need to do is build a calculated field called [City = City] which returns True when the value in the [City] field is equivalent to the selected value in the [CITY] parameter (and False otherwise). We’ll use this calculated field for colors and filters later.

Step One: Build a sheet that will act as the drop-down menu

Start by dragging the [City] field onto the Rows shelf and Tableau will populate a list of all cities in the dashboard.

Use your date field (from the joined Excel file) to filter to a particular month. You’ll later want to apply this filter to all the sheets on the dashboard so the month on the calendar matches the month filtered in the drop-down. Note that I also filtered the entire dataset to 2021 only.

Add the [City = City] field to the Filters shelf and exclude Null values.

Now your drop-down sheet (which I named “drop down”) should be a text list of all cities relevant to the month you’ve selected.

To mimic the highlighting function of a normal Tableau drop-down filter, we need to turn this sheet into a horizontal bar graph. This way, with some formatting tricks, we’ll be able to highlight the city that has been selected.

Add an AVG(1) calculation to the Columns shelf. Ensure that the Marks card is set to Bar. Set the axis to Fixed from 0 to 1 (so the bar reaches the far end of the sheet). Drag the size slider to a bit less than the maximum size. Remove gridlines, zero lines, column and row borders, axis rulers, and axis ticks. Turn off the Tooltip.

Finally, drag the [City = City] field to colors. Now when a city is selected from the parameter, the value of [City = City] will be forced to True, and the bar behind the selected city will highlight while the rest of the values will stay the same. To mimic the highlight colors of a normal drop-down filter, set True values to the hex color #D3D3D3 and False values to white (#FFFFFF).

Step Two: Add the drop-down sheet to the dashboard

Go to your dashboard and switch to floating objects. Drag a vertical container in the general area where you’d like to place your new drop-down, and then drag the drop-down sheet onto that floating container while holding Shift on your keyboard. Set it to Fit Width and hide the title.

Select the floating vertical container and from the drop-down menu select “Add Show/Hide Button”.

In the Edit Button menu, ensure that Image Button is selected. Tableau uses a downward pointing triangle as its drop-down menu indicator, which remains unchanged whether the drop-down is active or not. This means that we will need to find a similar icon and use that same image for both Button Appearances. Search any of your favorite icon sites for a filled down triangle in black and save it to your computer. I used this one from Flaticon. Choose this image for both Item Shown and Item Hidden and leave the rest of the settings in default. Your new button should look like the below image.

Step Three: Format the elements to look like a drop-down button

Mimicking the design of a normal filter or parameter drop-down button will allow this workaround to fit in seamlessly with others on your dashboard. Our goal is to design the workaround to look as close as possible to a regular parameter drop-down.

Start by inserting a vertical container into the area you’d like to place the drop-down (ignore the floating drop-down sheet for now). Add a text box and enter the text that describes the drop-down. (I titled mine “Workaround” for the purposes of this demonstration). Format this like a normal filter title: font set to Tableau Medium, size 8, and color set to black (#000000). Leave the default outer padding (4 pixels on all sides) and set the height to 25 pixels.

We will next need to create a simple sheet which displays the selected city within the drop-down button. Create a new sheet called “City” and drag the [City] field onto the Text mark. Filter [City = City] to True and you should be left with the text of the selected city from the parameter. Ensure the text font is set to Tableau Book in size 8 and turn off the Tooltip.

Next drag a horizontal container into the vertical container and underneath the text box. Put only the show/hide button into the horizontal container (note: do not put the container with the drop-down sheet in here). Drag the new City text sheet into the horizontal container to the left of the show/hide button. Set it to Fit Entire View and hide the title.

Then drag a blank sheet in between the City text sheet and the show/hide button. Set the default outer padding to 0 and set the Background fill to #E0E0E0. Then set the width to 1 pixel. This acts as the separator between the text and the show/hide button.

The horizontal container with the City text sheet, separator, and the show/hide button should be set to a fixed height of 20. Add 5 pixels of outer padding to the right and left of this container. You’ll also want to add a single line border in the hex color #C0C0C0.

Change the outer padding of the City text sheet to 0 pixels all sides. Set the width of the show/hide button to 18. Depending on the icon you used, you’ll need to play around with the padding to find the best fit. I used an inner padding of 4 pixels on all sides.

You should now have a drop-down button that looks nearly identical to the Tableau default. Move your floating container with the drop-down sheet under the button and adjust the width of this container to match the width of the button itself. Use the Position and Size up and down buttons in the Layout section to make incremental changes. You’ll also need to add a single line border to the floating drop-down sheet in the color #D4D4D4.

The height of the container will be a judgement call based on the number of values you have. By fitting the sheet to the width of the container, you will have a scroll bar for better usability.

Note that most of the sizing I used was what worked best to accommodate my dashboard. You may need to adjust these as necessary to suit your layout. I recommend dragging another filter or parameter drop-down onto the dashboard and using that as your template to determine sizes.

Also keep in mind that the sizing and positioning may not look exactly the same on Tableau Public, so you may need to keep readjusting.

One final formatting tip: float a blank dashboard object fitted perfectly on top of the City text sheet. This will dissuade users from clicking on the text to activate the drop-down, as this workaround button can only activate the drop-down menu via the show/hide button.

Step Four: Implement dashboard actions

The last step to building this workaround drop-down selector is to set up dashboard actions.

Add the Month filter from the drop-down sheet to the dashboard. Set that to filter to apply to all sheets on the dashboard. The filter which applies to your drop-down menu should now be the same filter that is applied to your calendar and your City text sheet.

Then add a parameter action on the drop-down source sheet. Run the action on Select. The Target Parameter is [CITY] and the Source field is [City] with no aggregation. Set “Changing the selection will” to Keep the Current Value.

Use the steps in this blog post by Luke Stanke to automatically deselect marks on the drop-down sheet. This will ensure that the pesky default highlighting that occurs upon clicking does not take away from the user experience.

That’s it!

Let me know if this workaround fits your use cases. I’m also available for any questions you may have. You can find me on Twitter at @KimTricker or on LinkedIn. You can also check out more of my work on my Tableau Public page!