Calculation by calculation - InsightViz Blog

A workbook's charts are the visible part and the easy part. Its calculated fields are a decade of business logic written by people who have mostly left, documented nowhere else, and load-bearing in ways nobody can enumerate.

So the useful question about any migration tool is not “does it convert calculations”. It is: which ones, how confidently, and what does it do about the rest.

sankey-beta
38 calculated fields,Translated,29
38 calculated fields,Translated - verify,6
38 calculated fields,Manual,3
Manual,LOOKUP or addressing-dependent,2
Manual,INCLUDE level of detail,1

One real workbook’s calculated fields. The middle band — six fields that converted but warrant a glance — is the one most migration tools do not report, and it is where wrong numbers come from.

Three verdicts, not two

Every calculation comes out of the importer with one of three statuses. The middle one matters most, and most tools do not have it.

Verdict	What it means	What you do
`translated`	Converted deterministically. No language model involved, so the same formula produces the same SQL every time and can be regression-tested.	Nothing
`translated-verify`	Converted, but something about it warrants a human glance — a date interval, a function that may not map one-to-one, or a reference to a field the extract does not contain.	One person, a few minutes each
`manual`	Not converted, with the reason. Nothing is guessed.	Model it, or accept the gap

A tool that reports only converted and failed is hiding the middle category, which is where the wrong numbers come from. Something that converted and might be subtly different is not a success.

A worked example

Six real calculated fields from one workbook

Taken from a nine-year-old regional sales workbook. Each is shown as the author wrote it, then as it comes out, then the verdict.

1. Null-safe margin. The most common shape in any estate.

ZN([Revenue]) - ZN([Cost]) → COALESCE(revenue, 0) - COALESCE(cost, 0) ✓ translated

2. A month bucket. Also everywhere.

DATETRUNC('month', [Order Date]) → date_trunc('month', order_date) ✓ translated

3. Days to ship. Converts, and asks for a glance.

DATEDIFF('day', [Order Date], [Ship Date]) → date_diff('day', order_date, ship_date) ⚠ translated — verify

Flagged because interval functions disagree at boundaries: whether an order placed at 23:50 and shipped at 00:10 is zero days or one. Usually it does not matter. At quarter ends, on an SLA report, it does.

4. Running total. A table calculation with a direction-independent meaning.

WINDOW_SUM(SUM([Sales])) → declared as running_total, emitted as a window pass outside the GROUP BY ✓ translated

5. Regional average, held constant. A FIXED level of detail.

{ FIXED [Region] : AVG([Order Value]) } → a derived table aggregated by region, joined back at that grain — which is what keeps it constant however the view is grouped ✓ translated

6. The one that stops.

IF ISMEMBEROF('Regional Managers') THEN [Region] = USERNAME() ELSE TRUE END → not translated ✗ reported

This is row-level security written as a formula. It could be translated and deliberately is not — see below.

Illustrative composite. The figures are internally consistent and every step is one the product supports today.

What translates cleanly

The deterministic tier is a rewrite from Tableau's function set to the engine's. It is unglamorous, testable code rather than a model guessing.

Tableau	Becomes	Note
`ZN(x)`	`COALESCE(x, 0)`	Direct
`IFNULL(a, b)`	`COALESCE(a, b)`	Direct
`DATETRUNC('month', d)`	`date_trunc('month', d)`	Direct
`DATEDIFF('day', a, b)`	`date_diff('day', a, b)`	Flagged to verify — interval semantics differ at boundaries
`IIF`, `CASE`, `IF/THEN/ELSE`	`CASE WHEN`	Direct
Arithmetic and comparison	The same	Direct
Nested aggregates over model fields	The equivalent expression	Direct, where the grain allows

Table calculations: translated, not approximated

A running total or a percent-of-total is a window over aggregated rows, not an aggregate. These translate into a declared calculation that the compiler emits as a window pass outside the grouping — which is what makes the number right.

In the workbook	Becomes	Status
`WINDOW_SUM(SUM([Sales]))`	`running_total`	Translated
`WINDOW_AVG(...)` with a window	`moving_average` over that window	Translated
`x / TOTAL(x)`	`percent_of_total`	Translated
`RANK()`, `RANK_DENSE()`	`rank`, `dense_rank`	Translated
`LOOKUP`, `PREVIOUS_VALUE`, `INDEX`, `FIRST`, `LAST`, `SIZE`	Nothing	Manual — these depend on the sheet’s addressing and partitioning, which is a property of the view rather than the data

Why addressing is the dividing line

A table calculation in Tableau is computed along a direction through the visible table — across then down, or restarting at each pane. Some calculations have a meaning independent of that direction and some do not. The ones that do translate; the ones that are defined by the addressing are reported, because guessing which direction the author meant is exactly the kind of thing that produces a plausible wrong number.

Level-of-detail expressions

FIXEDTranslated

{FIXED [Region] : SUM([Sales])} becomes a derived table aggregated at that grain and joined back. That join is precisely what keeps the value constant however the view is grouped — which is what FIXED means.

INCLUDE / EXCLUDEReported, deliberately

These are defined relative to the dimensions in the view. There is no single view at model-build time, so there is no correct answer to settle. Reported against the sheet, with the reason.

This is the honest boundary, and it is a property of the semantics rather than a limitation of effort. An INCLUDE expression means something different on each sheet it appears on. A model-level definition cannot encode “whatever is on the sheet”.

The category we refuse on purpose

One group of functions is not translated even though it technically could be, and this is a design decision worth explaining.

Function	What it does in Tableau	Why we do not translate it
`USERNAME()`	Returns the signed-in user, usually inside a filter.	Row-level security written as a calculation
`ISMEMBEROF()`	Tests group membership.	Same — entitlement hidden in a formula
`FULLNAME()`	The user’s display name.	Same family
`RAWSQL_*`	Passes a string through to the source database.	Arbitrary SQL cannot have policies compiled into it
`SCRIPT_*`	Calls out to R or Python.	An external runtime we do not host
`ATTR()`	Collapses to a value if unambiguous, else an asterisk.	A view-level construct with no model equivalent

Security should not live in a formula

USERNAME() inside a calculated field is how a great many Tableau estates implement row-level security, and it has a specific failure mode: the rule is invisible unless you open the workbook, and it only applies to the sheets that happen to use that field. Translating it would faithfully reproduce that weakness. Instead the importer reports it, and you write a row policy on the datasource — which applies to every dashboard, exploration, export and AI answer, including ones built years later. How that is set up →

This is the one place the importer deliberately makes you do work, and it is the place where doing the work has the largest payoff.

What the report actually gives you

The import page after a Tableau workbook: four of four sheets converted with zero partial and zero manual, four of four calculations translated with zero to verify, one table and one dashboard created — **Read the second number in each pair.** “4/4, 0 to verify, 0 manual” is a clean sample workbook. A real one comes back like “38/52, 9 to verify, 5 manual” — and that sentence is your fortnight.

flowchart TD
    C["A calculated field"] --> A{"Uses USERNAME, RAWSQL,
SCRIPT or ATTR?"}
    A -->|"yes"| R["Reported — model it properly
as a policy or a field"]
    A -->|"no"| B{"A table calculation?"}
    B -->|"needs the sheet's
addressing"| M["Manual, with the reason"]
    B -->|"direction-independent"| T1["Declared calculation"]
    B -->|"no"| L{"Level of detail?"}
    L -->|"FIXED"| T2["Derived table, joined at grain"]
    L -->|"INCLUDE / EXCLUDE"| M
    L -->|"no"| F["Function rewrite
→ translated, or verify"]
    style T1 fill:#e6f7f2,stroke:#9fd8c9
    style T2 fill:#e6f7f2,stroke:#9fd8c9
    style M fill:#fff4e6,stroke:#f0cb9a
    style R fill:#eef1ff,stroke:#c7cdf2

The decision tree the importer walks. Every leaf either produces SQL or produces a named reason — there is no branch that guesses.

3 verdictstranslated, verify, manual

0language models involved in the translation

Deterministicso the same formula always gives the same SQL

Namedevery calculation that was not converted

Working the list

Sort by how often the field is used, not by difficulty. A manual calculation on a sheet nobody opens is not work. Cross-reference with your usage data. Which is probably most of the estate →
Do the verify tier in one sitting. Each is a few minutes and they are mostly date boundaries. Doing them in a batch is far faster than one at a time.
Turn every USERNAME() into a policy. This is the highest-value item on the list every time, and it usually reveals that the old rule was applied less consistently than anyone believed.
Reconcile with an exported crosstab. Not by reading the SQL. Export from the original, compare cell for cell, and investigate every difference.

What we have not built

No language-model fallback tier. We could offer one for the manual set and have chosen not to yet, because a plausible translation of business logic is worse than an honest gap — and the reader cannot tell them apart.
No automated parity check. Comparing against an exported crosstab is currently your job. It should be the product’s, and it is the thing we would build next in this area.
Power BI DAX is not translated at all. Different language, different model, and the model layer needs the XMLA endpoint.

What else comes across → · The quarter you run both →

← All posts Request a demo