SquareX: the measure language

SquareX is DataSquares’ measure language. You define a measure once, at the data-model level, and it computes consistently everywhere the model is used — charts, dashboards, reports, exports, and the API. If you know Power BI’s DAX or Tableau’s LOD expressions, SquareX gives you the same class of power with a deliberately smaller, plainer surface: 39 functions, no implicit context transition, and everything compiles to SQL that runs in your own database.

Your first measures

A measure is a name, :=, and an expression:

Your first measures

Revenue := Sum(Sales.Amount)

Orders := CountDistinct(Sales.OrderId)

AvgOrderValue := Divide([Revenue], [Orders])

Three things to notice:

• Columns are plain dotted paths: Sales.Amount. No quotes, no brackets.
• Measures reference each other in square brackets: [Revenue]. Build small measures and compose them.
• Divide is safe by default — division by zero gives blank (NULL), not an error or infinity. The common intent is the default, not a gotcha.

Once saved, measures appear under ƒx Measures in the field tree — drag one onto a chart like any field, and it aggregates correctly at whatever grain the chart uses.

Filter context, and changing it with Calculate

A measure always evaluates inside the current filter context — whatever dashboard filters, chart filters, and grouping apply where it’s used. That’s what makes one definition reusable everywhere.

Calculate is the one function that modifies that context:

Changing the filter context

// Add a filter (intersects with whatever filters are already active)
OpenSRCount := Calculate(
    Count(ServiceRequest.Id),
    ServiceRequest.Status = "Open"
)

// Remove filters — the classic %-of-total denominator
PctOfTotalRevenue := Divide(
    [Revenue],
    Calculate([Revenue], RemoveFilters(Product, Customer))
)

• A bare predicate adds a filter. It intersects with the outer context — if the dashboard filters Region to “East” and your measure adds Region = "West", the result is empty, not overridden.
• RemoveFilters(…) lifts the named dimensions’ user filters (and their grouping), which is how you get totals. RemoveFilters() with no arguments is the grand total.

The override idiom

To override a filter rather than intersect with it, remove then add — removals always apply first, regardless of the order you write them:

// West revenue regardless of any dashboard Region filter
WestRevenue := Calculate([Revenue], Sales.Region = "West", RemoveFilters(Sales.Region))

Security guarantee

RemoveFilters can never touch row-level security. RLS filters are structural and sit beneath everything a measure can express — pinned by engine tests, not just policy.

Fixed-grain (LOD) measures

Fixed evaluates an expression at an explicit grain, regardless of the chart’s grouping — Tableau users know this as a FIXED level-of-detail expression:

Fixed-grain measures

CustomerRevenue := Fixed(Customer.Id, [Revenue])

// Date columns take an inline grain — no truncation boilerplate
CustomersPerMonth := Fixed(Date.Date:month, CountDistinct(Sales.CustomerId))

Put [CustomerRevenue] on a by-region chart with aggregation avg and you get average revenue per customer per region. One deliberate difference from Tableau: SquareX Fixed respects the filters active in the view — filters “just work” on it, no context-filter promotion required. (When you truly want the filter-independent number, that’s an explicit opt-in: Fixed(Customer.Id, [Revenue], IgnoreFilters).)

When the grain should follow the view instead of being pinned, Include and Exclude add or remove dimensions relative to whatever the chart groups by.

Fixed also composes with Calculate, both ways:

Composing Fixed with Calculate

// The whole fixed computation under a modified context
WestRevPerCustomer := Calculate(Fixed(Customer.Id, [Revenue]), Sales.Region = "West")

// Only the per-customer VALUES are West-filtered — the customer set
// still comes from the unmodified view context
PerCustomerWestRev := Fixed(Customer.Id, [WestRevenue])

Time intelligence

Time intelligence

// Same period last year
RevenueLY := Calculate([Revenue], DateShift(Date.Date, -1, "year"))

// Year-to-date accumulation (QTD and MTD work the same way)
RevenueYTD := YTD([Revenue], Date.Date)

// 12-month rolling total (missing months count as zero, not skipped)
Revenue12mRolling := RollingSum([Revenue], 12, "month")

Combine them for the standard comparison set: [Revenue], [RevenueLY], and Divide([Revenue] - [RevenueLY], [RevenueLY]) for year-over-year growth.

Time functions also compose with Calculate — wrap a YTD measure with a DateShift and you get prior-year YTD, aligned to this year’s buckets:

RevenueYTDLY := Calculate([RevenueYTD], DateShift(Date.Date, -1, "year"))

The same wrapping adds filters: Calculate([RevenueYTD], Region = "West") is a West-only accumulation, and Calculate([Rev12mRolling], …) works the same way. Measures that are themselves Calculate expressions nest too — your modifiers apply first, the measure’s own modifiers refine that context.

For balances — inventory on hand, account balances, headcount — summing over time is the wrong operation. LastNonEmpty and PeriodEndSnapshot pick the value at the right date instead:

Balances, not sums

ClosingStock := LastNonEmpty(Sum(Inventory.OnHand), Inventory.CountedAt)

EOMStock := PeriodEndSnapshot(Sum(Inventory.OnHand), Inventory.CountedAt, "month")

Accumulation needs additive inputs

YTD and RollingSum only accept additive expressions (sums and counts, and combinations of them). Asking for YTD([AvgOrderValue]) is rejected — a running sum of ratios is silently wrong — and the fix that says what you mean is Divide(YTD([Revenue]), YTD([Orders])). See SQX015.

Row-level math: the iterators

A measure normally has no row context — but line-level arithmetic like Qty × Price needs one. The iterators are the one explicit door:

Iterators

GrossRevenue := SumX(Sales, Sales.Qty * Sales.Price)

// The classic weighted average
WeightedPrice := Divide(SumX(Sales, Sales.Qty * Sales.Price), Sum(Sales.Qty))

Inside the row expression, bare columns of the iterated table are legal — and only there. AvgX, MinX, MaxX follow the same shape, and CountRows(table) counts rows at the query’s grain.

Branching and variables

Branching & variables

// Searched conditions — first true wins, no SWITCH(TRUE()) idiom needed
RevenueTier := Case(
    [Revenue] >= 1000000, "Tier 1",
    [Revenue] >= 100000,  "Tier 2",
    "Tier 3")

// Match a value
OrderVolumeBand := Switch([Orders], 0, "None", 1, "Single", "Multi")

// Name a sub-expression once, compute it once
MarginPct := Let(rev, [Revenue],
    Divide(rev - Sum(Sales.Cost), rev))

Iif(cond, then, else) covers the simple two-branch case, and // comments work anywhere — measures can document themselves.

From measure to SQL

There’s no separate calculation engine: every measure compiles into the chart’s own SQL query and runs in your database, with your row-level security applied. A context-modifying measure becomes a sibling CTE joined back to the chart’s rows:

SquareX

// On a bar chart grouped by product
PctOfTotal := Divide([Revenue], Calculate([Revenue], RemoveFilters(Product)))

Compiled SQL (simplified)

WITH "__sqx_0" AS (
  -- Revenue with Product lifted from filters AND grouping ⇒ the total
  SELECT SUM("Sales"."amount") AS "val"
  FROM "sales" AS "Sales"
)
SELECT "Product"."name",
       CASE WHEN MAX("__sqx_0"."val") = 0 OR MAX("__sqx_0"."val") IS NULL
            THEN NULL
            ELSE CAST(SUM("Sales"."amount") AS FLOAT) / MAX("__sqx_0"."val")
       END AS "PctOfTotal"
FROM "sales" AS "Sales"
JOIN "products" AS "Product" ON …
CROSS JOIN "__sqx_0"
GROUP BY "Product"."name"

You never have to take this on faith — the code editor shows the compiled SQL for the measure you’re writing, in your source’s own dialect. The heavier function pages each have an Under the hood section showing their shape: Calculate, Fixed, YTD, RollingSum, LastNonEmpty.

Two ways to author, one language

In the model editor, every measure can be authored two ways:

• Template builder — forms for the common shapes: aggregation, ratio, % of total, filtered measure, time comparison, to-date, rolling window, level of detail, percentile, branching. No syntax to learn; the builder shows the generated code as you go.
• Code editor — full SquareX with autocomplete (tables, columns, measures, functions), inline diagnostics as you type, format-on-save, and a compiled-SQL preview so you can see exactly what will run. Simple measures preview as a standalone expression; measures using Calculate, Fixed, or time intelligence show the full chart query they compile into — in your source’s own SQL dialect, with your row-level security included — since those shapes only exist inside a chart’s query plan.

Both edit the same definition. A measure built with templates opens in the builder; one that uses code-only shapes (like Let or the iterators) shows a read-only summary with an Edit as code path.

Things SquareX deliberately doesn’t have

• No implicit row context. A measure is aggregate-level; a bare column outside an aggregation is an error. Row expressions exist only inside the iterators (SumX, AvgX, MinX, MaxX) — one explicit door, no context transition, no EARLIER. This removes the single most confusing concept in DAX; simple row-level expressions can also live in calculated fields.
• No 250-function catalog. The surface is 39 curated functions; the escape hatch for exotic needs is SQL, not more functions.
• SQL semantics for NULL and types, not invented ones: aggregates skip NULLs, NULL + 5 is NULL (use Coalesce([Measure], 0) when you want zeros), and there’s no implicit string↔number coercion — format numbers with ToText before concatenating with &.

Keep going

RecipesCopy-paste measures for real questions: YoY, % of total, per-customer averages, inventory balances, P90s.

Coming from DAX or Tableau?Side-by-side translations — and the habits you get to unlearn.

Function referenceAll 39 functions — signature, when to use it, examples, and the SQL underneath.

Filter context & evaluationThe one mental model: the effective context, modifier order, NULL rules.

Syntax & grammarReferences, operators and precedence, comments, naming rules.

DiagnosticsSQX001–SQX015 explained, each with its fix.

Also related: worked examples (the acceptance corpus, annotated), calculated fields (row-level expressions, the layer beneath measures), and the REST API — measures are manageable programmatically via GET/POST/PUT/DELETE /api/models/:id/measures, plus …/measures/validate (diagnostics + compiled-SQL preview without saving) and …/measures/:mid/preview (see the Semantic Model tag).