How to Use Measure Names in Tableau Calculated Fields

Improve your users’ experience by integrating measure names in calculations

The introduction of parameter actions in Tableau 2019.2 has allowed the use of the Measure Names dimension within calculated fields for the first time. Learn how to use this new functionality to change KPIs with the click of a button!

Hi, this is Ryan with Playfair Data TV. In this video, I’m going to show you a quick trick that allows you to use the Measure Names generated field within calculated fields. The reason this is needed is there are times when you’re writing a calculated field where it would be much more elegant if we were able to use that generated field Measure Names right within the calculated field. But Tableau currently does not allow us to do that.

Now just to show you one of several examples, one of my favorite tactics that you’re looking at on the screen here is to parametrize the measure selection. I’ll show a parameter control and my end user can choose which metric is being shown on the view. So right now, it’s Discount. If I choose Profit, changes to Profit.

That’s not the specific point of this video. If you want to learn how to do that, you can learn how to allow users to choose dimensions and measures in another video. But just a quick review on how that tactic is implemented, because it will be important for this illustration, is there is a parameter with the names of all of my measure names. And then there is a calculated field that gives Tableau instructions for what to display when each of those allowable values is selected.

This formula– so take note– this isn’t even that bad, really. It’s got five different lines there explaining what to do. But in a real world scenario, you might have 50 different metrics or 50 different things you’re trying to parametrize. And it’d be a lot easier if I could just write this formula as something like IF Measure Names– notice it’s not even available to me as an option– but if Measure Names equaled my Measure Selected, then show the name of the metric– for example.

It’d be a lot easier, right? But as you can see, it’s not even available. However, it is here. So it’s in my data set, not available to me in calculated fields.

Well, that’s what this trick is all about. So what I’m going to do is set up a control sheet containing the names of my measure names. So I’m going to start a new worksheet and drag Measure Names to Rows. Measure Names cannot be used by itself. It has to be used in conjunction with Measure Values, but I don’t want to display my measure values on this chart.

So to get that data on the view without changing anything, I’m going to drag Measure Values to the Detail Marks Card. And now, you see the list populate, but I don’t have any values on here messing things up.

I also want the Measure Names to be on Text. So I’m going to drag Measure Names to Text. And hide the original header. And you can do that by right-clicking on the dimension and de-selecting Show Header. And you can obviously format this to your liking, but I’ll just make it a little bit bigger so we can see it on the video for now.

There’s a couple of measures that I don’t care for in this control sheet. Number of Records and my c. Measure Selected. So I’m just going to drag those off of the Measure Values Shelf and that will clean up my table a little bit. And I’ll also give this a name. This is my Control Sheet.

And here’s where the magic happens. This is the key point of this. Now that we can change values through a new dashboard action called Change Parameter (Value), we can use this control sheet with my line graph to say if you click one of these measure names, I want you to overwrite the allowable value in that Measure Selected parameter.

So to do that, both sheets need to be on a dashboard. So I’ll put my Control Sheet here on the left and my Line Graph here on the right. And I’ll leave the parameter control on here for now. Just clean that up a little bit. Maybe throw a blank object at the bottom. Push that up.

I’ll leave this parameter control on here for now to show you that this is working, but right now, we’re looking at Profit by Month.

But now, if I go to Dashboard, Actions, and click Add Action, here’s that new dashboard action I was telling you about. This is new as of version 2019.2.

Click on that and say if you click on the Control Sheet– so select a value on the control sheet– I want you to target the Measure Selected parameter and overwrite that Measure Selected parameter with the Measure Names field. So this is the key, that Measure Names field, which cannot be used in calculated fields currently, is now being used to overwrite that CASE WHEN logic, that parametrized version of my calculations that say if you click Discount, then show Discount. If you click Profit, then show Profit.

So we’re essentially using the Measure Names generated field in a calculated field. That’s what’s special about this. I’ll click OK.

Now if I click Discount, changes back to Discount. Notice that it overwrote the Measure Selected parameter control. I actually no longer need that because it’s now being controlled by this. It’s an even better user experience, in my opinion.

I click Profit, changes to Profit. Profit Ratio changes to Profit Ratio. And so on.

You can actually take this a step further and integrate this with custom buttons. So you can make those measure values or whatever you are parametrizing look very custom. Link the Measure Names field to that calculated field with a parameter control. And you’ve just used Measure Names within a calculated field.

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