A REAL Workaround for using Measure Names & Values in Calculations - Guest Post from Eli Blankers
This guest blog post is from Eli Blankers, a long-time Solutions Engineer at Tableau based in the Seattle area. Eli has been with Tableau for 11 years and has led hands-on training sessions at every Tableau Conference since 2015. Starting out as a "Product Consultant" in Tableau's earlier days, Eli still loves pushing the limits of what's possible in Tableau with counterintuitive calculations and clever tricks. Check out his portfolio of Tableau challenges on Tableau Public.
Before we proceed through this blog post, you can follow along using this workbook on Tableau Public.
There comes a time in every Tableau user’s journey, where you run into a surprise: you cannot reference Measure Names or Measure Values directly inside a calculated field. Try it — Tableau draws a red underline the moment you type either one. This classic realization always leads to the same inevitable and unsatisfying conclusion: pivot your data.
But we don’t want to pivot our data. The scenarios where we want to use Measure Names or Measure Values are often the exception, not the rule – and we don’t want to have an entirely separate data source or extract just to create this one visualization. But for years, most of us have surrendered to the belief that pivoting for this purpose is just a part of life.
And yet, unknown to the vast majority of Tableau users, there’s a technique that achieves exactly the same result, without pivoting – and it has been possible since 2015, though not truly discovered until the last few years.
This post walks through that technique — what I call Field Names / Field Values — including why the obvious approaches fail, how the solution works step by step, and where it opens up visualization possibilities that would otherwise require a completely separate, pivoted data source.
The Problem: why the obvious approaches fail
The motivating example is conditional formatting on a cross-tab. Imagine a table showing average discount, profit, quantity, and sales broken out by category and segment. The goal: color the text red only where the average discount is above 15% and the segment is Consumer or Home Office. Everything else stays black.
.png)
Your first instinct might be:
.png)
This calculation is valid and Tableau accepts it — but it doesn’t produce the right result. The entire Consumer column goes red across every measure, not just the Discount row. The reason is subtle: Measure Names is not a true dimension. It looks like one (it’s a blue pill, it breaks up the view), but it doesn’t operate at the level of detail you need. The level of detail in this view is Category/Segment – so all the measures within Furniture/Consumer are a single mark in the visualization, color and format applying across all those measures. This means targeting Discount independently from Profit, Quantity, or Sales isn’t possible in this way.
So your next thought might be to reference Measure Names directly in the calculation:
.png)
This time Tableau rejects it immediately — red underlines on both [Measure Names] and [Measure Values], with the error: “Reference to undefined field.” Those two fields simply cannot be used inside a calculation. That door is closed by design.
The Core Problem: To conditionally format individual cells in a measure-values cross-tab, you need Measure Names to behave like a real dimension. It doesn't. So the solution is to build one that does – entirely through calculations, no pivot required.
The Insight: forget measure names, just make four buckets
Before writing a single calculation, it helps to step back from the Tableau-specific framing entirely. What we actually need is a calculated dimension that produces one value per measure row — something Tableau will treat as a real dimension that we can reference in other calculations. The content of that dimension doesn’t matter at all. It just needs four distinct labels, and every cell in the view needs to have data for its label. That’s it. Once we have that fake dimension in place, we can build a companion measure that maps each label back to its real aggregate value — and from there, the conditional formatting logic is straightforward.
Kudos: Casey O'Donnell came up with the original solution to this challenge. With her collaboration, we were able to expand the idea further with even more flexible methods in creating these Dummy Fields.
Step 1 — Create [Random Values]
We need a way to assign every row of data to one of four buckets reliably, with data present in every cell. The key word is every — if any cell in the view comes up empty, the view will have gaps where we need values. This is the data density requirement, and it’s the reason the bucketing method matters.
Tableau has an undocumented function, RANDOM(), that returns a value between 0 and 1 for each row. It’s not in the function list, but it works. However, by default RANDOM() computes at the mark level rather than the data row level. To push it down to individual rows, we add a self-cancelling measure reference:
Random Values
RANDOM() + [Sales] - [Sales]
The + [Sales] - [Sales] part nets to zero mathematically but anchors the random computation at the row level. The result is a random decimal between 0 and 1 assigned independently to every row of data — which we’ll use in the next step to split all rows roughly evenly across four buckets.
Quick Note: RANDOM() is undocumented and unsupported by Tableau – it could change in a future release. Additional methods are shared at the end of post.
Step 2 — Create [Field Names]
With a row-level random number in hand, we can now assign each row to a named bucket. The bucket names are named after the measure names we want to represent in the view:
Field Names
IF [Random Values] <.25 THEN 'Discount'
ELSEIF [Random Values] <.5 THEN 'Profit'
ELSEIF [Random Values] <.75 THEN 'Quantity'
ELSE 'Sales'
END
The result is a calculated field that Tableau treats as a real dimension — a blue pill you can place on Rows or Columns just like any other dimension, and reference directly inside other calculations. That’s the whole point. The random assignment is arbitrary – if you brought a regular Measure into the view with [Field Names], all the values would be shuffled; what matters is that every cell in the view has data, and that we now have a dimension we can reason with in calculations.
Step 3 — Create [Field Values]
Now we need a companion measure that returns the correct aggregated value for each bucket, ignoring the random bucketing that was used to create [Field Names]. This is where LOD expressions do the heavy lifting — EXCLUDE [Field Names] strips out the artificial dimension and computes the real aggregate at the natural level of detail (category + segment in this example):
Field Values
IF [Field Names] = 'Discount' THEN { EXCLUDE [Field Names]: AVG([Discount]) }*100
ELSEIF [Field Names] = 'Profit' THEN { EXCLUDE [Field Names]: SUM([Profit]) }
ELSEIF [Field Names] = 'Quantity' THEN { EXCLUDE [Field Names]: SUM([Quantity]) }
ELSEIF [Field Names] = 'Sales' THEN { EXCLUDE [Field Names]: SUM([Sales]) }
END
Step 4 — Create the formatting calculation
With the three fields in place, the conditional formatting logic is finally expressible — and clean. Remember the goal is to color the text red only where the average discount is above 15% and the segment is Consumer or Home Office. Everything else stays black.:
Formatting
IF MIN([Field Names]) = 'Discount' AND
(MIN([Segment]) = 'Consumer' OR MIN([Segment]) = 'Home Office')
AND MIN([Field Values]) >= 15 THEN 'Red'
ELSE 'Black'
END
The MIN() wrappers are needed because the LOD expressions in [Field Values] return aggregates, and Tableau requires consistent aggregation levels across a calculation. The MIN() of a single-value dimension at a given cell just returns that value — it doesn’t change the result, it just satisfies Tableau’s aggregation requirements.
Putting it all together
In the view, replace Measure Names on the Rows shelf with [Field Names], and replace Measure Values on the Text shelf with [Field Values]. Drop the formatting calculation onto the Color shelf and set the color encoding to match your red/black strings. The cross-tab will look and behave identically to the original — except now the conditional formatting works exactly as intended, targeting only the 15% or higher Discounts for Consumer and Home Office segments.
Where else this applies
Once you understand the pattern, you start seeing it everywhere. Two use cases worth calling out specifically:
Waterfall charts across separate measures
.png)
If your data has separate columns for financial forecast categories — Closed, IN, UP+, UP-, BCO, C&C Unseen — building a waterfall normally requires pivoting those columns into rows first. With Field Names / Field Values, you can build that waterfall directly in the same data source, keeping all your other sheets unaffected.
Grouped metrics with role-based visibility
.png)
Because [Field Names] is a real calculated dimension, Tableau lets you group its values and create hierarchies. You can group metrics by audience (Individual Contributor, Manager, Corporate) and surface different field groups to different dashboard consumers — all from a single data source, no separate extracts required.
Trade-offs and the data density problem
The biggest practical risk with this approach is data density. For the view to render correctly, every cell — every combination of category, segment, and field name — needs to have at least one row of data assigned to it. If a bucket ends up empty for a given cell, that cell will be blank in the view. This isn’t a hypothetical edge case; it’s something you’ll actually encounter depending on your data, your filters, and your bucketing method.
Here are three bucketing approaches that each have different density characteristics:
In practice, RANDOM() is a reasonable choice for dense data use cases, which is why it’s the approach implemented in this example.
I should also mention that all the values contained within Field Values will require the same formatting.
Want more challenge problems in Tableau?
This solution is a Jedi-level challenge in Eli’s Challenge Workbooks, which contains hundreds of challenges across five difficulty tiers. A great way to stress-test your Tableau skills.
Thanks for reading!
Eli Blankers
───────────────────────
No comments: