How to Allow Users to Choose Dimensions and Measures in Tableau

A favorite tactic for designing an intuitive user experience

Learn how to leverage Tableau parameters and CASE / WHEN logic to let your users choose the measures and/or dimensions being visualized. This approach is useful even when your audience doesn’t know how to use Tableau!

Hi, this is Ryan with Playfair Data TV. And in this video, I’m going to show you one of my very favorite tactics in Tableau, which is to allow my users to choose custom measures and/or dimensions. I’m going to show you what I mean by that over in Tableau Desktop.

Let’s say we’re going to start with an example on allowing the user to select measures. Right now, I’ve got a three-row line graph that looks at sales, profit ratio, and a formula that I made called AOV, which is short for average order value, by continuous month of Order Date. It’s not a bad chart, it’s a nice line graph, but it’s starting to get a little bit busy, and there’s a lot on one view. Instead, what I’d prefer is to allow my user to choose on their own whether they want to plot Sales, Profit Ratio, or Average Order Value. That’s what I’m going to show you how to do first.

There’s two primary steps involved with this. The first thing we need to do is create a parameter with our measure choices in it. So I will click on this down arrow in the top right corner of the Data pane and click Create Parameter. And I will call this Measure Selected.

The next thing you have to do within a parameter is give that field a data type. In our case, we’re eventually going to allow the user to choose from three different words– Sales, Profit Ratio, and AOV. So the data type should be string, because those are words or text. We also have a very specific list of words or text. So I’ll click this List radio button and type out those three measure options. So Sales, Profit Ratio, and AOV.

One nuance or pitfall of this technique is that these allowable values have to exactly match the calculated field in the second step. Because of that, I typically take a screenshot of what’s on the view, or maybe I’ll write down what these values are, because they have to match exactly. Obviously, they’re typo-sensitive, so if there’s a typo, they won’t match. They’re even case-sensitive. So if, in one example, you have a capital S for Sales, in the second step you use a lowercase s for sales, they won’t match.

Probably the biggest pitfall, though, is if you accidentally type a space after one of these values, that also will not match. So you have to be very careful, very precise. But let’s assume I’ve taken a screenshot of this and know exactly how they’re spelled.

I’ll go ahead and click OK, because that is the first step in its entirety. It creates a parameter for me. That parameter does almost nothing on its own. I’ll show you towards the end of the video one thing that it does. But more often than not, you have to incorporate parameter values within a calculated field to give Tableau instructions for what to do with that value.

So the second step is to create a calculated field. And I’ll call this c. Measure Selected. The c dot, by the way, is just my own naming convention to help me know which version is the calculated field and which version is the parameter. You can use whatever naming convention you’d like. You can call this something different if you’d like.

Just so you know, you are allowed in Tableau to have a calculated field named the same exact thing as a parameter. But it can be a little bit confusing, which is why I type this c dot before the calculated version, just so I can tell a difference between the two.

And for this example, we’re going to use CASE WHEN logic. We could also use IF/THEN logic, but CASE WHEN logic for this exact tactic is a little bit more elegant. It’s a little bit more efficient in how you write the code. It starts with the function case, CASE, space, the name of my parameter.

Make this is a little bit bigger so that we can see it. And then on each line, with a WHEN statement, I’m going to give Tableau instructions for what to display when each of those three words is selected. So on the first line, I’m going to say WHEN quote Sales, THEN SUM of Sales.

Whenever I’m using this type of logic, I like to type one WHEN statement first, and then this CASE WHEN logic always concludes with the word END. But the reason I go ahead and type END after a single line of logic is because I want to make sure I’m on the right track. I’d rather know if something is off from the beginning, rather than type out 10 lines of WHEN statements and then figure out something’s wrong, because now I have to troubleshoot 10 lines of code instead of just one.

But now that I know I’m on the right track, I’ll go ahead and type in the other two options. We have WHEN quote Profit Ratio. And the reason I’m using quotation marks is because the data type is string. So that’s why I’m putting those in quotes. When profit ratio is selected, I want to show Profit Ratio, which is a calculated field that comes with the Sample – Superstore data set.

Notice we have a mix, or at least what appears to be a mix, of aggregations going on. On the top row, we have SUM of Sales. On the second row, we have Profit Ratio with no aggregation again, or so it appears. But if I click Profit Ratio, I can look over here in the data dictionary to see the underlying calculated field.

The underlying calculation is actually SUM of Profit divided by SUM of Sales. So the aggregation is happening within the calculation. Because we have matching aggregations, this formula is working just fine. Just pointing that out in case you get confused on why Tableau is letting you use a sum for one of them, but nothing for the second one.

The third and final option was AOV, which is a calculated field I made before the video. So AOV, again, no aggregation because, if I click on it and click this flyout, I can see that the formula for Average Order Value, which has been abbreviated to AOV, is SUM of Sales divided by COUNTD of Order ID. That is the entire formula for step two. I will share this in the related content below the video. But we are done with both steps, so I’ll click OK and go ahead and add this to the view.

I’m going to put this as the first row so that we can quality check the results. Anytime you’re using parameters in order to transfer the control of the allowable value to the user so that they get to choose it, you have to do what’s called Show Parameter Control. That’s been abbreviated recently in updates in Tableau to just Show Parameter. But to do so, you right click on the parameter and click Show Parameter. Or if you’re using a older version of Tableau, it will say Show Parameter Control.

If you click on that, you’ll see a dialogue appear in the top right corner. That is a parameter control. It will include the allowable values that you coded in step one. So in this first case, Sales, Profit Ratio, and AOV. If I click that dropdown, we should see those three options– Sales, Profit Ratio, AOV. And we do.

Notice the current value of the parameter is Sales. So what we should see when we added the c. Measure Selected calculation to the first row is we should see that the first row and the second row match because they’re both showing SUM of Sales by continuous month of Order Date. And they do. But now we can use this as a quick quality assurance step. We can change the dropdown to Profit Ratio, and when we do so, we should see the first row match the third row. And we do. And if I click AOV, the first row should match the fourth row. And it does.

So now that I know that we’re in good shape, I can go ahead and remove the final three rows and just allow my user to decide what should the four, or three measures they want to see at a time. So Sales, Profit Ratio, and AOV. And what I think is particularly nice about this is our user doesn’t have to know anything about authoring in Tableau Desktop. We are coding this user experience for them, allowing them to choose for themselves what’s relevant to them, without making them have to know how to code anything. They just need to know how to use this dropdown menu to get the visualization that they would like.

Before I move on to showing you how to do something similar with dimensions, I wanted to show you the one thing that the parameter itself does. You can think of this parameter value as being a extra column in your data source that’s just hard coded or static at the current value for every single row. So right now, you can imagine a column in our data set called Measure Selected. And on every single row, it’s just going to say AOV. Just a single value.

So it doesn’t do much on its own, but one nice thing that you can use it for is as an axis header. Notice right now it says c. Measure Selected. If I put Measure Selected, the parameter, onto the Rows Shelf, it will say the name of the current value of the parameter before it shows the axis title.

And in fact, that axis title isn’t really relevant for my user, so I can hide that by right clicking on the axis, clicking Edit Axis, and then just clearing this box where it says Title. If I clear that out, my axis gets cleaned up a little bit. The title goes away, but I still have the Measure Selected parameter in front of the axis.

You can also rotate that label by right clicking on it and clicking Rotate Label. And I’ll get rid of this header by right clicking and hiding. That’s called a Field Label. So I’ll hide that, and just kind of cleaning this up. My user doesn’t know it, but we’ve cleaned it up so it’s a more friendly user experience for them. Now, if I flip to Sales, we should see that word change to Sales. If I flip to Profit Ratio, it will change to Profit Ratio.

All right, we’re going to do the same thing on a second sheet, but this time use three different dimensions. We’ll say that we want to allow the user to choose between the Segment dimension, Category dimension, and Region dimensions that come with the Sample – Superstore dataset.

For this one, I’m going to start again by creating a parameter. So I will right click this time in any blank space on the Data pane and click Create Parameter. And this one I will call Dim Selected. The data type once again is string. We once again have a very specific list of strings. So I will click the List radio button and type out those three things– Segment, Category, Region.

And I happen to have three options with both the measure version and the dimension version. Just want to point out you can use as long of a list as you would like. In fact, if you left this as All, you could use any strings at all. It’d be infinite possibilities for the allowable values. But to put some kind of restraint on this, we’re going to allow the user to choose from these three words. That’s the entire step one. So I will click OK.

Again, this does almost nothing on its own. I want to make a calculated field with CASE WHEN logic that gives Tableau instructions for what to do with each of those three dimensions. So I will call this c. Dim Selected. And it starts with CASE Dim Selected parameter. That turns purple, telling me that that is a parameter. WHEN Segment is selected, then show the Segment dimension.

I’m going to go ahead and type END, and point out a couple of things here. So I’m on the right track. Calculation’s valid. Notice this time that there’s no aggregation before our orange field. We’re not summing anything up this time. We’re just saying when the Segment dimension is selected, I want Tableau to use the Segment dimension. So no aggregations for any of these. When Category is selected, then I want Tableau to show the Category dimension, and when Region is selected, then I want Tableau to use the Region dimension.

And that is the entire formula. I will go ahead and click OK. For this one, I’m just going to make a simple bar chart that looks at Sales on the Rows Shelf and my newly created c. Dim Selected on the Columns Shelf. I’ll make this a little bit wider so that we can read the axis titles.

So we’ve got Consumer, Corporate, and Home Office. Those sound like segments from our Sample – Superstore data set. To be sure that we’re looking at Sales by Segment, I’m going to right click on my Dim Selected parameter and click Show Parameter. And sure enough, the current value is Segment.

So we’ve got Sales being broken down by Segment. But again, we’ve just unlocked this great user experience that allows the user to choose for themselves whether they want to look at Sales by Segment or they can flip this to Sales by Category. And you will see the titles change. We now have Furniture, Office Supplies, and Technology.

And you don’t even need the same number of dimension members between the allowable values. Those first two options happened to have three different dimension members, but Region has four. So if I change this to Region, we’ll see Sales by Region. And now we see Central, East, South, and West show up as our dimension members.

So we’ve looked at allowing users to choose custom measures, custom dimensions. You can also do these at the same time. So instead of hard coding SUM of Sales here, I could put in my Measure Selected instead by dragging the field right on top of SUM of Sales, and show that parameter control.

And now, we’ve got nine different choices for our user. They can look at three different dimensions and three different measures. So we could flip this back to Sales. We’ll see those bars change. We could flip this back to Segment. We’ve now got Sales by Segment. Or we could change the measure to AOV and change the dimension to Category, and now we’re looking at Average Order Value by Category.

And again, this is such a powerful user experience because the user doesn’t need to know how to do any of this stuff. We’ve coded it for them, given them a very flexible way to look at only the measures and the dimensions that are relevant to them.

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