Skip to content
DataSquaresDocs

Calculated fields

A calculated field is an extended column defined on a data model table via a SQL expression over that table’s physical columns. Once created, it behaves identically to any physical column: it can be dragged into chart field wells, filtered, grouped, aggregated, and formatted. In the field tree and model views, calculated fields are identified with an ƒx icon.

Where to create and edit calculated fields

Section titled “Where to create and edit calculated fields”

You can author and edit calculated fields from two locations in DataSquares:

  1. In the Model Editor (/models/:id):
    • Click any field on a table node to open the side Field Configuration panel.
    • Use Add Calculated Field to write a new expression, or select an existing calculated field from the list to edit its formula, data type, aggregation, or visibility.
  2. In the Dashboard Canvas Field Tree:
    • While designing a dashboard, hover over a model table in the left field tree.
    • Click ƒx → New calculated field (or right-click an existing calculated field to edit) to open the modal dialog without leaving your canvas.
    • Calculated fields are instantly available for dragging into chart field wells, filters, and tooltips across the canvas.

Configuration options

Section titled “Configuration options”
Property Description
Field name The identifier shown in the field tree and chart field wells (e.g., margin_pct or full_name).
Expression SQL expression evaluated by the database engine over this table’s columns. Use the Insert column helper to quickly insert validated column names.
Result type Defines output casting: number, integer, decimal, string, boolean, date, datetime, time, or geo.
Default aggregation Default rollup applied when dropped into a measure well: none, sum, avg, count, count_distinct, min, or max.
Hidden flag Toggle to hide the field from default field trees and chart selectors when used only for intermediate modeling.

Server-side validation

Section titled “Server-side validation”

Click Validate to evaluate the SQL expression against the database before saving.

Validation runs through POST /api/models/:id/validate-expression scoped to the table:

  • Dialect-aware syntax check: Validates expressions against the source database engine’s parser.
  • Table schema verification: Confirms that every referenced column exists and is visible on the target table.
  • Fail-safe injection protection: Prevents DML, subqueries, or unsafe write routines from being stored.
  • Immediate feedback: Returns structured error reasons (valid: false, reason: string) to pinpoint syntax errors or misspelled columns before saving.

Expression rules & best practices

Section titled “Expression rules & best practices”

Because expressions push down directly into the source database, write expressions in your database’s native dialect:

  • Row-level arithmetic: 
    price * (1 - discount_rate)
    (Pair with default aggregation sum or avg)
  • String concatenation & formatting: 
    first_name || ' ' || last_name
    (Pair with default aggregation none)
  • Safe division: Always protect against division by zero using NULLIF: 
    SUM(revenue - cost) / NULLIF(SUM(revenue), 0)
    (Pair with default aggregation none)
  • Case statements & bucketing: 
    CASE WHEN amount > 1000 THEN 'High' ELSE 'Standard' END

Calculated field vs. SquareX measure

Section titled “Calculated field vs. SquareX measure”
Capability Calculated Field SquareX Measure
Scope Single table column Workspace / Model calculation
Evaluation Row-level or table-scoped SQL expression Filter-context-aware measure engine
Cross-table logic Evaluated within table boundary Multi-table navigation, Calculate, Fixed
Time intelligence Raw SQL date functions Built-in SamePeriodLastYear, YTD, ParallelPeriod
Join keys Cannot be used as relationship join keys Measures are computed over relationships
Section titled “Related”
© 2026 DataSquares