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 a dashboard’s field tree, calculated fields are marked with an ƒx. (One exception: a chart whose join repeats rows can’t group by a calculated field; see Joins & fan-out.)

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 one: Field Name, Expression (with Insert column…), Result type and Default aggregation, then Validate and Save Field.
   • The panel lists the table’s calculated fields under Calculated fields (N). Select one to edit it (Edit calculated field: Name, Formula, Data type, Default aggregation, Hidden (exclude from field pickers) and Sensitivity, then Save changes), or delete it. Used by lists the cards, metrics and measures that read it; a field still in use can’t be deleted. Changing the formula of one that a governed metric reads makes the metric’s next version and clears its certification.
2. In the Dashboard Canvas Field Tree:
   • While designing a dashboard, point at a table in Data’s Fields tab, on the rail, and click its ƒx (New calculated field) to open the dialog without leaving your canvas: Field name, Expression, Result type and Default aggregation, then Create field. Saving runs Validate first and stops if the expression is not valid.
   • To edit a calculated field, click the ƒx on its row, or press F2 on it.
   • Calculated fields are instantly available in chart field wells, filters, and tooltips across the canvas: click one to add it, or drag it, like any field.

Field settings and a calculated field, step by step

An example: net order value after discount.

1. Select a column in a table to open Field Configuration. Set the display name, data type and default aggregation (how the field is totalled on a chart), and hide it from dashboards if you like.

   

2. Choose Add Calculated Field. Name it and write the formula in SquareX using the table's columns. Insert column adds a column name for you.

   

3. Choose Validate to check the formula before you save it. A problem is reported in plain words.

   

4. Save adds the calculated field to the table. It appears beside the real columns and can be used in any card. Before you delete a field, DataSquares lists what reads it.

   

Configuration options

Property	Description
Field name	The identifier shown in the field tree and chart field wells (e.g., margin_pct or full_name). It must differ, ignoring case, from the names of the table’s other fields and the columns they read: charts and filters find a field by its name, so a second field by that name would take over what reads the first.
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 (exclude from field pickers)	In the model editor’s edit form only: hides the field from field trees and chart selectors when it is used only for intermediate modeling. A new field starts visible.
Sensitivity	In the model editor’s edit form: a sensitivity label (pii, confidential, restricted) that masks the field for people who may not read it; only a company admin changes it.

Server-side validation

Click Validate to check the expression before saving; the result reads Expression is valid or Invalid expression with the reason.

Validation runs through POST /api/models/:id/validate-expression (any member may call it; an expression is at most 4,000 characters there):

• Syntax check: The expression is parsed with a SQL grammar for the source’s database, and, if that grammar refuses it, with the PostgreSQL and MySQL grammars before it is called invalid. A syntax error says where: Syntax error at position N: unexpected “…”., or Syntax error: the expression is incomplete. The database itself is not asked.
• Column check: Every column the expression names must be a column of the table: one of its fields’ columns or any column discovered on it, hidden ones included (Unknown column “…” — not a column of this table.). Date-part words such as year, month, dow and epoch are not taken for columns. Another calculated field is not a column, so an expression can’t refer to one. The dashboard dialog’s Validate runs this check; the model editor’s Validate checks the syntax only, and saving runs both.
• Fail-safe injection protection: Refuses an empty expression, a ;, a comment, unbalanced parentheses, an open quote, and keywords that write or reach outside a query (such as DROP, DELETE, INSERT, UPDATE, EXEC, PG_SLEEP, DBLINK or UNION SELECT): Expression contains a forbidden keyword.
• Its own table only: An expression reads the columns of the table it belongs to and nothing else. A subquery, a WITH, UNION or LATERAL, another table or schema, a comment (in any form the source’s engine reads), and functions that reach outside the row (a warehouse’s table, file, URL and remote functions, dictionary lookups, query_to_xml, dblink, EXTERNAL_QUERY and the like) are refused when you save, naming what was found, and again whenever the field is queried. So is a function your database defines, called with its schema or package (dbo.fn_region_total('East')), and a method called on a column (SQL Server’s xml.value()): the engine’s own prefixed functions, such as PostgreSQL’s pg_catalog. or BigQuery’s SAFE., still work. On a PostgreSQL, DuckDB or ClickHouse source, a function called without its schema must be one of the database’s built-in functions (on PostgreSQL also those of the extensions that ship with it, such as pgcrypto, pg_trgm, fuzzystrmatch, unaccent and uuid-ossp): any other, your own or an extension’s such as PostGIS’s, is refused; use it through a view in your database that returns its result as a column. This keeps row-level security whole: no expression can read rows a policy hides. The same rule covers a transform’s added column and a row-level-security policy’s condition.
• Immediate feedback: Returns structured error reasons (valid: false, reason: string) to pinpoint syntax errors or misspelled columns before saving.
• Names: Saving a field whose name another field of the table already has, or that names a column another field reads, is refused (409) with the name to change.

Adding a calculated field needs the models create permission, changing one the edit permission, and deleting one the delete permission.

Expression rules & best practices

DataSquares has no expression language of its own: an expression is your database’s SQL, with its functions and operators, sent to the database inside the query. 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

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

Choosing between fields and measures

• Use a Calculated Field for per-row transformations, string cleansing, custom dimensions, or table-level expressions.
• Use a SquareX Measure when your calculation must dynamically adapt to dashboard filters, compute cross-table ratios (like % of Total), or leverage time intelligence.

Related

• Data models & relationships — configuring semantic models and table schemas.
• Joins & fan-out — understanding how aggregations interact with joins.
• SquareX: the measure language — defining advanced context-aware metrics.