Skip to content
DataSquaresDocs

Calculate

Context

Calculate(expr, modifier…)

Evaluates expr with the active filter context changed by its modifiers. Three kinds of modifier:

  • a bare predicate (Sales.Region = "West") — adds a filter, intersecting with the outer context (KeepFilters(pred) is an accepted alias);
  • RemoveFilters(dims…) — lifts user filters and grouping on the named dimensions;
  • DateShift(col, n, unit) — shifts the date context.

Modifiers apply in category order — removals first, then added predicates, then date shifts — regardless of the order you write them. That’s what makes the override idiom work.

Nesting. Calculate composes over another Calculate (written directly or reached through measure references): your modifiers transform the context first, then the wrapped measure’s own modifiers transform that — so Calculate([WestRevenue], RemoveFilters(Sales.Region)) still filters to West (the measure’s own predicate applies after your removal), while wrapping a remove-filters measure with a new Region predicate leaves it immune (its removal clears what you added). Calculate also wraps YTD and RollingSum — see prior-year YTD on the YTD page — and Fixed in both directions.

When to use it

Section titled “When to use it”
  • Filtered KPI variants: open tickets, West-region revenue, premium-tier sales — one base measure, many slices.
  • Comparisons that must ignore a dashboard filter (the override idiom below).
  • Time comparisons via DateShift, and %-of-total denominators via RemoveFilters.

Example

Section titled “Example”
// Override any dashboard Region filter (intersect would give empty):
WestRevenue := Calculate([Revenue], Sales.Region = "West", RemoveFilters(Sales.Region))


// Nesting: filter an already-filtered measure — both predicates intersect
WestBikes := Calculate([WestRevenue], Product.Category = "Bikes")

Without the RemoveFilters, an outer filter Region = "East" would intersect with Region = "West" and yield blank — adds never override, they narrow.

Under the hood

Section titled “Under the hood”

A modified context can’t ride the chart’s own GROUP BY, so the measure computes in a sibling CTE with its own WHERE/GROUP BY, joined back to the chart’s rows:

// On a bar chart grouped by region
WestRevenue := Calculate([Revenue], Sales.Region = "West", RemoveFilters(Sales.Region))

When only filters change (no removals), the sibling keeps the chart’s group keys and joins back on them instead of a CROSS JOIN. Row-level security compiles inside the sibling too — no modifier can reach beneath it.

Good to know

Section titled “Good to know”
  • Row-level security is untouchable: no combination of modifiers can widen data past RLS.
  • A predicate referencing a bare column is only legal inside Calculate — outside it, bare columns violate aggregate closure (SQX014).
  • The nested operand must be a single measure or Calculate/YTD/RollingSum call — a context function embedded in arithmetic under Calculate (Calculate(YTD(…) / 2, …)) is SQX010: split it into its own measure and reference it.
Section titled “Related”
© 2026 DataSquares