# Calculated Fields ## Overview A calculated field is an expression-based value derived from your data — for example, a discount ratio, a concatenated label, or a running total. Calculated fields are not a query-native object: a query never *defines* a stored field. Instead, a query either references a calculated field that already lives in the Semantic Data Model (by its API name) or carries an inline, query-scoped calculation in the request body. In both cases the value is computed at query time and is never persisted to the underlying tables. There are two roles, mirroring the two authoring entities: a *calculated dimension* produces a non-numeric, non-aggregatable value used to classify or group rows, and a *calculated measurement* produces a numeric value with an aggregation method. Use a model calculated field when the calculation is reusable and governed centrally; use an on-the-fly calculated field when the calculation is specific to one request and you do not want to change the model. You can reference a calculated field almost anywhere an expression is accepted — in `fields`, in `filter`, in `aggregate_filter` (HAVING), in a sort order, and as a grouping axis. The formula grammar itself (functions, operators, level-of-detail syntax) is shared with the rest of the expression language. ## Metadata in the model Model calculated fields are defined at authoring time as one of two entities: a `SemanticCalculatedDimension` (a non-aggregatable, expression-based dimension) or a `SemanticCalculatedMeasurement` (a numeric calculation with a default aggregation type, data type, and decimal places). Each carries an `expression`, a `syntax` (formula dialect), and an optional set of filters that scope the data the expression sees. A query references either one by its `apiName`. For how to define these entities, see [Semantic Calculated Dimension](/docs/data/semantic-layer/guide/connect-api-in-depth-entities.md#semantic-calculated-dimension-semanticcalcdimension) and [Semantic Calculated Measurement](/docs/data/semantic-layer/guide/connect-api-in-depth-entities.md#semantic-calculated-measurement-semanticcalcmeasurement) in the Authoring API. On-the-fly calculated fields have no model metadata — they are declared entirely within a single query request. ## Query usage ### Reference a model calculated field Reference a persisted calculated field the same way you reference any model field: with a `semantic_field` expression carrying the field's `apiName`. The model defines the formula; the query only names it. A model calculated measurement can reference other calculated fields, so its expression can be nested. Optionally set `semantic_aggregation_method` to override the field's default aggregation for this query. ```json { "structured_semantic_query": { "fields": [ { "expression": { "semantic_field": { "name": "formula_measurement_example" } }, "semantic_aggregation_method": "SEMANTIC_AGGREGATION_METHOD_SUM" } // ... ], "options": { "detailed_rows": true } }, "semantic_model": { "apiName": "Sales", "label": "Sales", // ... semanticDataObjects ... "semanticCalculatedMeasurements": [ { "apiName": "formula_measurement_example", "label": "formula_measurement_example", "dataType": "Number", "decimalPlace": 3, "expression": "10 * inner_formula_measurement_example", "syntax": "Salesforce" }, { "apiName": "inner_formula_measurement_example", "label": "inner_formula_measurement_example", "dataType": "Number", "decimalPlace": 3, "expression": "100 + SemanticAccount__dlm.semantic__Number__c", "syntax": "Salesforce" } ] } } ``` ### Define an on-the-fly calculated field Carry a calculation that exists only for this request as an inline `calculated_field` object inside a `QueryField` expression. It never touches the model. A `calculated_field` declares a `name`, an `expression`, and its output kind: a numeric measurement uses `calculated_measure_expression`, and a non-numeric dimension uses `calculated_dimension_expression` (see the next two sub-sections). For a measurement, set `semantic_aggregation_method` to control aggregation; `SEMANTIC_AGGREGATION_METHOD_USER_AGG` treats the expression as already aggregated, which is also permitted for a formula that evaluates to a static number such as `POWER(2,2)`. ```json { "structured_semantic_query": { "fields": [ { "expression": { "calculated_field": { "name": "simple_power_calc", "expression": "POWER(2,2)", "calculated_measure_expression": { "measure_output_type": "SEMANTIC_MEASUREMENT_TYPE_NUMBER" } } }, "semantic_aggregation_method": "SEMANTIC_AGGREGATION_METHOD_SUM", "alias": "simple_power_calc" } ], "options": { "detailed_rows": true, "grand_total": true, "row_counts": true } }, "semantic_model": { "apiName": "Sales", "label": "Sales" // ... } } ``` ### Request shape A `calculated_field` object carries: - `name` — a label for the calculation within the query. - `expression` — the formula string. - `syntax` — the formula dialect (`"Tua"` or `"Salesforce"`); optional. - exactly one output declaration: `calculated_measure_expression` (numeric) or `calculated_dimension_expression` (non-numeric). For a measurement, `calculated_measure_expression` may include `decimal_places_options.decimal_places`. To use an on-the-fly *dimension* as a grouping axis, set `grouping: "ROW_GROUPING"` on the enclosing `QueryField` (see [Grouping](/docs/data/semantic-layer/guide/query-api-in-depth-grouping.md)). The example below selects one dimension calc as a grouping axis and one measure calc aggregated with `USER_AGG`. ```json { "structuredSemanticQuery": { "fields": [ { "alias": "simple calc", "expression": { "calculated_field": { "name": "simple-calc", "expression": "[SemanticAccount__dlm].[sdm__LastModifiedDate__c]", "calculated_dimension_expression": { "dimension_output_type": "SEMANTIC_DIMENSION_TYPE_DATE_TIME" } } }, "grouping": "ROW_GROUPING" }, { "expression": { "calculated_field": { "name": "summary-calc-in-select", "expression": "SUM([SemanticAccount__dlm].[sdm__AccountTypeId__c])", "calculated_measure_expression": { "measure_output_type": "SEMANTIC_MEASUREMENT_TYPE_NUMBER", "decimal_places_options": { "decimal_places": 2 } } } }, "semantic_aggregation_method": "SEMANTIC_AGGREGATION_METHOD_USER_AGG" } // ... ] }, "semantic_model": { // ... } } ``` ### Dimension vs. measurement output types The output declaration determines whether the calculated field behaves as a dimension or a measurement: - `calculated_dimension_expression.dimension_output_type` — a `SEMANTIC_DIMENSION_TYPE_*` value such as `SEMANTIC_DIMENSION_TYPE_TEXT` or `SEMANTIC_DIMENSION_TYPE_DATE_TIME`. Dimension calcs are not aggregated. - `calculated_measure_expression.measure_output_type` — a `SEMANTIC_MEASUREMENT_TYPE_*` value such as `SEMANTIC_MEASUREMENT_TYPE_NUMBER`. Pair it with a `semantic_aggregation_method`. The example below declares a text dimension calc (used inside a filter): ```json { "structured_semantic_query": { "fields": [ // ... ], "context_filter": { "simple_dimension_filter": { "unary_predicate": { "expression": { "calculated_field": { "name": "formula_on_the_fly_example", "expression": "UPPER([SemanticAccount__dlm].[sdm__AccountSource__c])", "syntax": "Tua", "calculated_dimension_expression": { "dimension_output_type": "SEMANTIC_DIMENSION_TYPE_TEXT" } } }, "unary_operator": "UNARY_OPERATOR_IS_NOT_NULL" } } } }, "semantic_model": { // ... } } ``` ### Reference fields in a formula How a formula names a data field depends on the dialect: - **Tua** wraps each identifier in brackets: `[Table].[Field]` — for example `COUNTD([SemanticAccount__dlm].[semantic__AccountSource__c])`. - **Salesforce** uses a dotted, unbracketed form: `Table.Field` — for example `100 + SemanticAccount__dlm.semantic__Number__c`. A formula can reference raw fields and other calculated fields by name. ```json { "structured_semantic_query": { "fields": [ { "expression": { "calculated_field": { "name": "countd", "expression": "COUNTD([SemanticAccount__dlm].[semantic__AccountSource__c])", "calculated_measure_expression": { "measure_output_type": "SEMANTIC_MEASUREMENT_TYPE_NUMBER" } } }, "semantic_aggregation_method": "SEMANTIC_AGGREGATION_METHOD_USER_AGG", "alias": "Count Distinct" } ], "options": { "detailed_rows": true, "grand_total": true, "row_counts": true } }, "semantic_model": { "apiName": "Sales", "label": "Sales" // ... } } ``` ### Set the formula dialect Set `syntax` on a `calculated_field` to choose the formula dialect: `"Tua"` or `"Salesforce"`. On-the-fly calculations use the Tua grammar by default; set `syntax` to `"Salesforce"` to write the expression in the Salesforce dialect. Dialects can interoperate — a Salesforce-syntax calc can reference a model calculated field authored in Tua syntax by its API name. ```json { "structured_semantic_query": { "fields": [ { "expression": { "calculated_field": { "name": "sf_calc_referring_to_tua_calc", "expression": "tua_model_calc + 123", "syntax": "Salesforce", "calculated_measure_expression": { "measure_output_type": "SEMANTIC_MEASUREMENT_TYPE_NUMBER" } } }, "semantic_aggregation_method": "SEMANTIC_AGGREGATION_METHOD_USER_AGG", "alias": "SF referring to TUA" } ] }, "semantic_model": { "apiName": "Sales", "label": "Sales", // ... "semanticCalculatedMeasurements": [ { "apiName": "tua_model_calc", "label": "tua_model_calc", "dataType": "Number", "decimalPlace": 3, "expression": "SUM([SemanticAccount__dlm].[semantic__Number__c])", "syntax": "Tua" } ] } } ``` ### Level-of-detail (LOD) expressions A level-of-detail expression computes an aggregation at a granularity that differs from the query's selected dimensions. It has the form `{ FIXED | INCLUDE | EXCLUDE [Dim1], [Dim2], ... : AGG([Measure]) }`: - **FIXED** computes the aggregation at the listed dimensions only, ignoring the view's dimensions. - **INCLUDE** adds the listed dimensions to the view's granularity (finer). - **EXCLUDE** removes dimensions from the view's granularity (coarser). LOD expressions are written in the Tua dialect and can be nested up to six levels deep. Only **FIXED** LOD expressions can be used as a calculated *dimension*. The following model calculated dimension uses nested FIXED LOD expressions: ```json { "semanticModel": { "apiName": "Sales_Insights_KPI_Semantics", // ... "semanticCalculatedDimensions": [ { "apiName": "Last_Stage_RI_clc", "label": "Last Stage", "dataType": "Text", "expression": "IF { FIXED [Opportunity].[Opportunity_Id]: COUNT([Opportunity_History].[Opportunity_History_Id]) } > 1 AND [Opportunity_History].[Opportunity_History_Id] = { FIXED [Opportunity].[Opportunity_Id]: MAX([Opportunity_History].[Opportunity_History_Id]) } THEN \"true\" \nELSE \"false\"\nEND", "level": "Row" } // ... ] } } ``` > **Performance:** A `FIXED` LOD is computed before dimension filters, so ordinary dimension filters do not reduce the rows it scans — they apply only to the LOD's dimension subquery, not to its calculation. `INCLUDE` and `EXCLUDE` LODs are computed after dimension filters, so those filters do apply to the calculation. To reduce a `FIXED` LOD's scan, use a context filter (which applies before it) or an `INCLUDE`/`EXCLUDE` LOD instead. > **Performance:** Each LOD expression generates its own subquery joined back to the query, and nesting LODs compounds this. Use the fewest and shallowest LODs that express the calculation. > **Performance:** A filter that references an LOD expression disables the window-function optimization and forces a subquery-and-join plan. ### Where calculated fields can appear Reference a calculated field in select `fields`, in `filter`, in `aggregate_filter` (HAVING), in a sort order, and as a grouping axis. In `aggregate_filter`, the calculation must be an aggregated measure so the predicate runs after aggregation, like a SQL HAVING clause. ```json { "structured_semantic_query": { "fields": [ // ... ], "aggregate_filter": { "binary_predicate": { "left_expression": { "calculated_field": { "name": "summary", "expression": "1+sum(AccountSemanticLayer__dll.AnnualRevenue__c)", "calculated_measure_expression": { "measure_output_type": "SEMANTIC_MEASUREMENT_TYPE_NUMBER" } } }, "binary_operator": "BINARY_OPERATOR_GREATER_THAN", "right_expression": { "int_expression": 5 } } } }, "semanticModelApiName": "test_model" } ``` ## Reference | Field (wire name) | Type | Required | Description | | ------------------------------------------------------- | ----------------------------- | -------- | ------------------------------------------------------------------------------------------------------------- | | `calculated_field` | CalculatedField | N | An inline (on-the-fly) or referenced calculation within an expression. | | `calculated_field.name` | String | Y | Name of the calculation within the query. | | `calculated_field.expression` | String | Y | The formula string. | | `calculated_field.syntax` | String (enum) | N | Formula dialect: `"Tua"` (default for on-the-fly) or `"Salesforce"`. | | `calculated_measure_expression` | CalculatedMeasureExpression | N* | Declares a numeric output; carries `measure_output_type` and optional `decimal_places_options`. | | `calculated_measure_expression.measure_output_type` | String (enum) | Y | `SEMANTIC_MEASUREMENT_TYPE_*` value (e.g. `SEMANTIC_MEASUREMENT_TYPE_NUMBER`). | | `calculated_dimension_expression` | CalculatedDimensionExpression | N* | Declares a non-numeric output; carries `dimension_output_type`. | | `calculated_dimension_expression.dimension_output_type` | String (enum) | Y | `SEMANTIC_DIMENSION_TYPE_*` value (e.g. `SEMANTIC_DIMENSION_TYPE_TEXT`, `SEMANTIC_DIMENSION_TYPE_DATE_TIME`). | | `semantic_field.name` | String | Y | API name used to reference a model calculated field. | \* Exactly one of `calculated_measure_expression` or `calculated_dimension_expression` is set per `calculated_field`. For the full request schema, see [Request Reference](/docs/data/semantic-layer/guide/query-api-in-depth-fields.md). ## Limitations - **On-the-fly calculated fields are never persisted.** They exist only within the scope of the request that carries them. - **A summary-level formula field may only use `SEMANTIC_AGGREGATION_METHOD_NONE`, `SEMANTIC_AGGREGATION_METHOD_AUTO`, or `SEMANTIC_AGGREGATION_METHOD_USER_AGG`.** `USER_AGG` is also allowed for a formula that evaluates to a static number with no field references (for example `123`, `INT(1.0)`, `PI() + 4`), which the query treats as already aggregated. - **LOD expressions are supported only in the Tua dialect.** - **Only FIXED LOD expressions can be used as a calculated dimension.** INCLUDE and EXCLUDE depend on the view's dimensions and cannot be dimensions themselves. - **Recursive LOD nesting is capped at six levels.** - **LOD expressions cannot be used in join criteria.** - **LOD expressions are not supported with hard-join models.** - **LOD expressions are not supported when `detailed_rows` is `true`.** Aggregated fields and aggregate (HAVING) filters are likewise disallowed when `detailed_rows` is `true`. ## Related - [Semantic Calculated Dimension](/docs/data/semantic-layer/guide/connect-api-in-depth-entities.md#semantic-calculated-dimension-semanticcalcdimension) — Authoring API for defining calculated dimensions. - [Semantic Calculated Measurement](/docs/data/semantic-layer/guide/connect-api-in-depth-entities.md#semantic-calculated-measurement-semanticcalcmeasurement) — Authoring API for defining calculated measurements. - [Functions & Formulas](/docs/data/semantic-layer/guide/query-api-in-depth-functions.md) — The formula language used inside calculated field expressions. - [Filtering](/docs/data/semantic-layer/guide/query-api-in-depth-filtering.md) — Using calculated fields in `filter` and `aggregate_filter` (HAVING). - [Grouping](/docs/data/semantic-layer/guide/query-api-in-depth-grouping.md) — Using a calculated dimension as a grouping axis with `grouping: "ROW_GROUPING"`. - [Aggregation & Totals](/docs/data/semantic-layer/guide/query-api-in-depth-aggregation.md) — Aggregation methods, including `USER_AGG`, applied to calculated measurements. - [Request Reference](/docs/data/semantic-layer/guide/query-api-in-depth-fields.md) — Complete field schema.