Online tableau training, tableau tips, & video tutorials
How to Allow Users to Choose Dimensions and Measures in TableauPreview
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 be sharing one of my very favorite tactics, which is to allow your end users to choose from different measures and dimensions from within a Tableau dashboard.
The reason that I like this tactic so much is it transfers the control of the analysis from yourself to the end user without them even having to know how to use Tableau. They’ll just need to eventually need to know how to use a dropdown menu. It’s as simple as that.
To get started over here in Tableau Desktop, the first thing that you have to do is create a parameter. I like to create a parameter by right clicking in any blank space on the Data Pane. If you don’t see any blank space, you can hover between any of the different areas and create blank space. If you don’t like to create that blank space, you can also click this down arrow in the top right corner of the Data Pane, and you’ll see the option there to create a parameter from there as well.
But I just click in this blank space and click create parameter. We’ll pretend for this first use case that I’m sharing that we’re going to eventually allow our end users to look at either sales, profit ratio, or a newly created calculated field for average order value. The formula for average order value, or AOV, is simply sum of sales divided by the number of unique orders.
So those will be the three options. We’ll eventually put those onto a line graph, and let our end user choose which metric is being visualized. Because we’re eventually going to allow our end user to choose from three different words: sales, profit ratio, or AOV, the data type in this case is going to be string.
I’ll also give this a better name than parameter three. The sample superstore dataset comes with two parameters. That’s why this is the third parameter we’re creating. I’m going to go ahead and call this p. Measure Selected. And you can have your own naming convention. I like to put that p. there just to specify whether it’s a parameter or a calculated field.
So p. Measure Selected, and then I’m going to choose a list of allowable values. The reason I’m choosing list is because we have a very specific list of choices. And I’m going to type those three words as each of the values, sales, profit ratio, and AOV, which is short again for average order value.
It’s very important to note exactly how your parameter allowable values are spelled. This is very sensitive later on when we go to add this within a calculated field to tell Tableau what to do with each one of these choices. It needs to be spelled exactly the same, including casing.
Otherwise, Tableau won’t be able to recognize what value has been selected and what it should do with that value. So just take note of that. Sometimes, I take a screenshot of it or I’ll write it on a piece of paper just so I know exactly how it’s spelled and what casing I used. I’m going to click OK.
Parameters do almost nothing on their own. I will show you one thing at the end of this video that you can use a parameter for by itself. But on its own, I’m trying to drag this on the view, and it’s not even letting me drop it anywhere. It does almost nothing.
Almost every time, you have to integrate a parameter within a calculated field to give Tableau instructions for what to do with each allowable value that’s been selected. So I’m going to create a calculated field, and I call this c. Measure Selected.
And that’s where that naming convention comes in the p. versus c. You can technically name a calculation the same thing as a parameter, but it gets a little bit confusing when you’re trying to find which field to use within a calculated field. So I like to put a c.
And the syntax for this one, I’m going to use CASE logic. You could get away with if then logic. But in this case, because we don’t want to repeat the parameter name over and over again, CASE when logic is slightly more elegant and slightly more efficient.
The syntax is the word CASE, C-A-S-E, followed by whatever you’re referencing. We’re referencing p. Measure Selected. If the field turns purple within a calculated field, you know that Tableau recognized that field as a parameter. That’s what that color coding represents.
On the next line, I’m going to type when and our first allowable value, which was sales. And the reason I use quotation marks is because the data type is string. When sales is selected, I want Tableau to show the sum of sales.
With CASE when logic, I like to type one line of code, and then CASE when logic always concludes with the word end. So I go ahead and type that in. And the reason I do it now is I want to do a quick spot check to make sure I’m on the right track. I would rather know if I’ve got an error after one line of code rather than typing out 10 lines of code only to find out there was an error in it, and then I’ve got to review 10 lines of code instead of just one.
So now that I know I’m on the right track, I’ll go ahead and type in the other two allowable values. When profit ratio then profit ratio. And the last one was when AOV then AOV.
This is the only formula that does not come in the sample superstore dataset, by the way. But anytime you see orange in a calculated field dialog box, if that field is a calculated field, when you click on it, you will see on the right hand side the underlying formula for that. So this was a calculated field that I created before I started filming the video, and it says sum of sales divided by COUNTD Order ID.
One other thing to point out about this before I close this window, it appears that we have a mix of aggregated fields and non-aggregated fields, which typically causes an error message. You can’t mix the two. You can’t have some fields being aggregated and others not being aggregated.
But the difference here, it’s not visible on the screen itself, but both profit ratio and AOV are calculated fields. And again, like I mentioned, if you click on either one of those, you can see the underlying formula and see that it’s already being aggregated. That’s why we did not have to further aggregate it.
In fact, if we tried to, that’s when we would run into an error message. If I tried to sum up profit ratio or take an average of it, it’ll tell me that it’s already being aggregated and I can’t aggregate it further.
So this was the correct formula. I’m going to go ahead and click OK. And now, I’m just going to make a line graph using my newly created c. Measure Selected. I’m going to put c. Measure Selected onto the Rows Shelf.
I’m going to hold down the right mouse key and drag order date to the Columns Shelf, which will allow me to choose the date part as well as if my date’s being used as discrete or continuous before it even draws anything. So I’m going to choose month continuous, and we’ve got our line graph.
What we’re seeing right now on the screen is sales by continuous month of order date. The reason it chose to show sales first is because that was the first allowable value, and it became the current value. You can confirm that by right clicking on the parameter and clicking edit, and there’s a box that says current value.
You can also quality check this by putting the original metric on the Rows Shelf as a second row. If I put sales on the Rows Shelf as well, these two trends should match. The first row is pulling the sales value from that parameter we created. The second row is the original sales value. So it’s just a way to quickly spot check, make sure it’s working.
Now that I know it’s working, I’ll remove sales. And there’s one last step with parameters that you just have to remember. If you want to transfer the control of that allowable value from yourself to your end user, you have to do what’s called show parameter control.
And you can do that by right clicking on the parameter and clicking show parameter control. You’ll see a filter looking box pop up in the top right corner. But that’s not technically a filter.
That is called a parameter control, and it will contain the allowable values that you coded in that first step. So these are the three words that we allowed our end users to choose from, sales, profit ratio, and AOV. In the second step, when we created a calculated field, we gave Tableau instructions for what to do when each of those three words is selected.
So as we’ve already seen, sales is working. But now if I click profit ratio, profit ratio is what’s being visualized on the view. If I click AOV, AOV is what’s being visualized on the view.
This is very powerful, because our end user, again, they don’t need to know how to use Tableau Desktop. You have coded this user experience for them. And now they can drill down to only what’s relevant to them. There’s lots of applications for this that we’ll see in other videos.
One last thing, I mentioned parameters do almost nothing on their own. The one thing that they can help with is to provide an axis title. I can drag p. Measure Selected to the Rows Shelf, and the word that is selected is what becomes the axis title.
There’s some additional formatting I could do to make this look a little bit nicer. This doesn’t look very nice to my end user, c. Measure Selected. So I’ll right click on that axis and click edit axis.
And one of the things I can edit on this axis is the title. I’ll delete the original title and close this box. You can see it’s already starting to look a little bit nicer.
I also might want to rotate this axis title. I can do that by right clicking and clicking rotate label. And now, that’s made one other thing appear that I don’t necessarily want. I’ll also right click on that and hide that field label for the rows. So a slightly nicer axis title.
And now, when I choose a different word in the parameter control, that’s the word that gets populated as the axis title. So right now, it’s AOV. If I click sales, it shows sales as the axis title and as the line graph. If I choose profit ratio, the axis title changes to profit ratio, and we see profit ratio being trended out on our line graph.
This has been Ryan with Playfair Data TV – thanks for watching!
Related video: An Introduction to Tableau Parameters
Related video: How to Make a Line Graph in Tableau
Related blog post: How to Let Users Choose Measures and Dimensions in Tableau
Related chapter: Practical Tableau – Chapter 64 – Allows Users to Chooses Measures and Dimensions
- How to Dynamically Format Numbers in Tableau
- How to Conditionally Format Individual Rows and Columns in Tableau Like You Can in Excel
- How to Allow Users to Choose Dimensions and Measures in Tableau
- Exercise: Sales by Category, Region, Segment, or Ship Mode
- Using Tableau’s INDEX() Function for Easier Top N Filtering