How measures run

A measure is one aggregation, such as sum("Sales"."Amount"), written once on a semantic model and used by every chart that shows it. The chart supplies the rows, the joins and the filters; the measure supplies the calculation, and this page explains the rules behind that split.

What runs when a chart shows a measure

• A measure is one aggregate expression. It never writes its own FROM, JOIN, WHERE or GROUP BY; the chart supplies all four.
• When you save a measure, the expression is run in the shape a chart will use it in, over no rows. A bare column that is not inside an aggregate fails there, not later at chart time.
• Every chart that runs measures starts from the model's fact table (its core table), and a model without a fact table cannot save measures. The table and its relationships are set on the model; see Relationships and the core table.
• Other tables join with LEFT ANY JOIN, whatever cardinality the relationship stores. The join keeps at most one row from a joined table per fact row, so it can never multiply your result; when the joined key is not unique, the one matching row it picks is arbitrary.
• Relationships are walked from the fact table, and the first path that reaches a table is the one used. A many-to-many relation is modelled through a junction BI table.
• A filter whose table sits on the many side of a join path is applied inside the join, so it matches any qualifying row of that table instead of the one arbitrary row the join picked.
• Measure references are inlined. A chart declares the selected measures and every measure they reference, and each referenced name is substituted with its expression rather than computed once and reused. A reference cycle is refused when you save, and a measure that points at a deleted or invalid measure stays out of charts until the reference works again.
• The AI that writes and repairs a measure's expression is on Write DataPrism measures with AI; the editor around it is on Measure editor. The panel that reads, changes and tests measures is Model AI.

Aggregates, windows and PARTITION BY ALL

• Every raw column reference sits inside an aggregate. Use the conditional forms: sumIf("Sales"."Amount", "Sales"."Region" = 'West'), countIf("Sales"."Status" = 'Returned'). A condition wrapped around an aggregate, such as if(cond, sum(...), 0), fails when you save.
• An aggregate inside another aggregate is illegal, directly or through an inlined reference. Windows run after aggregation, so a window wraps the aggregate: sum(sum("Sales"."Amount")) OVER ().
• PARTITION BY ALL stands in for the chart's grouping. Per window it becomes the chart's group-by columns minus that window's ORDER BY columns, or tuple() when none remain, so one measure adapts to every chart.
• A windowed measure needs its window's columns among the chart's group-by fields, and a chart that does not group by them is refused. The refusal and what to do about it are on Write DataPrism measures with AI.
• ORDER BY names the chart's period columns as bare names, for example ORDER BY "Year". A scalar-wrapped column does not give a monthly window on a monthly chart, and a measure name written there is read as a group-by column instead: write the aggregate out, as in rank() OVER (ORDER BY sum("Sales"."Amount") DESC).
• Do not combine PARTITION BY ALL with a metric ORDER BY: the macro keeps every group-by column, so each partition holds one row and every rank comes out 1. A PARTITION BY ALL window with no ORDER BY gives one row per partition as well.
• A windowed measure cannot be windowed again. It may be post-processed with scalar functions only, such as round("Revenue", 2).
• An explicit partition compares within the columns you name: a lagInFrame window over PARTITION BY "Month" ORDER BY "Year" reads the same month of the previous year.
• A running total is a windowed measure: sum("Revenue") OVER (PARTITION BY ALL ORDER BY "Month" ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW).

Filters, QUALIFY and shares

• Report filters, chart field filters and row-level security are applied before aggregation. A measure cannot escape or override them.
• Windows see the data before your filters. A filter on a group-by column is applied after the windows (a QUALIFY step) unless every involved window already partitions by that column. That is why a share of a total is read against the unfiltered total.
• In practice, a chart filtered to one year still lets a previous-period window read the year before it.
• Row-level security is the exception: it is applied before aggregation for every measure and never moves after the windows. See Row-level security and View as.

Totals and KPI cards

• A total is not computed by adding the rows above it. A subtotal or grand total runs the query again at that coarser grain, with the summed-out group-by fields removed. Plain sum and count totals match the sum of the rows; a ratio, an average or a distinct count does not.
• A KPI or Cards visual has no group-bys. A windowed measure there gets a hidden timeline: numeric values are summed across the qualifying periods, and other types take the latest period's value.
• A card can therefore show a larger number than the same measure on a grouped chart: a window shape that stays constant across the timeline is multiplied by the number of periods. Filter the timeline columns on the card, or keep the measure additive, such as a lagInFrame shape.
• The cards themselves are on KPI and Cards; matrix totals are on Matrix visual.

Helper measures and references

• Splitting logic into smaller helper measures is encouraged. A helper is referenced by its bare name, inlined into every measure that uses it, and tested on its own before anything builds on it: on a card and by its period group-by.
• Allowed: scalar composition and rounding ("Gross Profit" - "Operating Cost", round("Revenue", 2)), conditions over measures, and a non-windowed measure as the argument of a new window (it inlines to an aggregate).
• Not allowed: a measure inside a new aggregate without a window, a measure in a window's ORDER BY or PARTITION BY (write the aggregate out), and windowing a windowed measure again.
• Keep a chain to one window shape. When a measure builds on a windowed helper, the helper's window requirements apply to every chart that shows the measure.
• A helper that no longer works leaves the measures built on it out of charts until it works again. Tests and their runs are on Measure tests.

Result types and NULLs

• A measure's result is one of these types: whole_number, decimal_number, date, date_time, time, duration, boolean or text. The type comes from the expression's result and becomes the measure's value type.
• A result whose type is not one of them is refused when you save, and the message names the type. A Map result, for example, is refused.
• Divide with a guard: toFloat64(sum("Sales"."Amount")) / nullIf(count(), 0). Without nullIf, a zero denominator gives inf, nan or a failed query instead of an empty value.
• Aggregates skip empty values: count("Sales"."Customer Id") counts the rows where that column is not empty; count() counts rows.
• Percentages multiply by 100 and round. Divide as Float64 rather than between Decimal values when you want fractional percentages: a Decimal division keeps a fixed scale, so 12.345 % can show as 12.00.
• When an edit changes the result type, the saved format resets to the new type's default. Check the format after a type change.

Patterns that return wrong numbers silently

These mistakes return a number without any error. Check them before you trust a result:

• A dimension key that is not unique: the join picks one matching row, and every value read from that table is then arbitrary.
• An average of averages: compute the ratio at the base grain, for example toFloat64(sum("Sales"."Amount")) / nullIf(count(), 0), or weight it.
• Counting over a fan-out: count() counts fact rows, never the many side of a relationship.
• Adding distinct counts across groups: run the distinct count again at the coarser grain.
• A division without a guard: without nullIf on the denominator, a zero leaks as inf or nan.
• An ambiguous bare column: two joined tables share the column name (AMBIGUOUS_IDENTIFIER); qualify the reference with its table.
• PARTITION BY ALL without ORDER BY: every partition holds one row.
• A share read under a filter: it still reads the unfiltered total, by design.
• A truncated, unsorted result: a grouped result cut at its row limit without a sort is an arbitrary subset of its groups.
• Totals read as sums: totals are not sums for ratios, averages and distinct counts.
• A period shift over missing periods: a row-based shift reads an older period when one is missing, for example 2024 showing as the previous year of 2026.
• A snapshot summed across dates: daily balances summed over a month give a multiple of the real balance. Use a warehouse flag column with sumIf, or a latest-period pattern.
• A literal date for a relative period: write the period with today(); a literal date freezes while the measure is compiled again on every chart run.

Questions, answered

Why is every rank 1 in my chart?

The window combines PARTITION BY ALL with a metric ORDER BY. The macro keeps every group-by column, so each partition holds exactly one row and every rank is 1. Write the ranked aggregate out instead, without the macro: rank() OVER (ORDER BY sum("Sales"."Amount") DESC).

Why is a total different from the sum of the rows above it?

A total is computed by running the query again at its own, coarser grain, with the summed-out group-by fields removed, not by adding the rows. Plain sum and count totals match the sum of the rows; ratios, averages and distinct counts do not. See Matrix visual.

Why does my filtered share percentage show the unfiltered share?

By design: windows see the data before your filters, so a share of total is computed against the unfiltered total. A filter on a group-by column is applied after the windows unless every involved window already partitions by that column.

Why does a dimension value come out arbitrary?

The dimension's key is not unique, so the join picked one of several matching rows for that fact row. Make the key unique, or read the value from a dimension whose key is unique.

Why can I not wrap a windowed measure in another window?

Windows run after aggregation, and a window inside another window is illegal. A windowed measure may be post-processed with scalar functions only, such as round("Revenue", 2). To change what the window computes, edit the window inside that measure.

Why does my KPI card show more than the same measure on a chart?

A card has no group-bys, so a windowed measure is collapsed onto a hidden timeline: numeric values are summed across the qualifying periods. A window shape that stays constant across the timeline is multiplied by the number of periods on a card. Filter the timeline columns on the card, or keep the measure additive, such as a lagInFrame shape. See KPI and Cards.

Why does my measure fail with an illegal aggregation error?

One aggregate sits inside another, directly or through a reference that inlines into it. Windows run after aggregation, so let the window wrap the aggregate: sum(sum("Sales"."Amount")) OVER (...). A comparison against a windowed value goes in an outer if() on the grouped row.

Which mistakes return wrong numbers with no error at all?

The wrong-pattern list on this page covers them all: a non-unique dimension key, an average of averages, counting over a fan-out, added distinct counts, a division without nullIf, an ambiguous bare column, PARTITION BY ALL without ORDER BY, a share read under a filter, a truncated unsorted result, totals read as sums, a period shift over missing periods, a snapshot summed across dates, and a literal date for a relative period.

Which result types can a measure return?

One of these: whole_number, decimal_number, date, date_time, time, duration, boolean or text. A result whose type is not one of them is refused when you save, and the message names the type.

How do I show the value at the latest period?

An aggregate cannot sit inside another aggregate, and windows run after aggregation, so the latest period cannot be picked inside the sum itself. Two patterns work:

• On a chart grouped by the period column, zero every other period in a window: if("Month" = max("Month") OVER (ORDER BY "Month" ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), sum("Sales"."Amount"), 0). On a card the pattern collapses to the latest period's value. A filter on the period column hides its row, so the measure reads 0 instead of moving the latest period.
• When the value must stay the same under filters, use a flag column written in the warehouse, such as Is Current Month, with sumIf("Sales"."Amount", "Sales"."Is Current Month" = 1).

Does referencing a measure compute it once?

No. A referenced name is substituted with its expression, so a helper used by two measures is computed as part of each of them, not once and shared. The chart declares the selected measures and everything they reference, sorted by name, so a helper appears in the query only when a shown measure reaches it.

Do I need to write the table name before a column?

Yes, qualify a column with its BI table name, such as "Sales"."Amount". A bare name keeps every joined table that has such a column, and as soon as two of them share it the reference becomes ambiguous (AMBIGUOUS_IDENTIFIER). Two things stay bare: the period columns in a window's ORDER BY and PARTITION BY, which name the chart's output columns, and a referenced measure, which is always written by its own name.