Functions & Formulas

Overview

Formulas let a semantic query compute values that are not stored directly in the model. You write a formula as an expression string — combining field references, operators, literals, and functions — and the Semantic Query API evaluates it as part of the query. Formulas power calculated dimensions and calculated measurements: a calculated dimension groups or filters rows by a computed value, while a calculated measurement aggregates a computed value.

There are two ways to bring a formula into a query:

• Model-defined calculated fields. The formula is authored once in the Semantic Data Model as a SemanticCalculatedDimension or SemanticCalculatedMeasurement, and the query references it by name through a semantic_field expression.
• On-the-fly calculated fields. The formula is supplied directly in the request as a calculated_field expression, without being persisted to the model. Use this for query-scoped computations.

Formula expressions use the Tua dialect, which references fields with bracket notation [Table].[Field] and supports level-of-detail (LOD) expressions and table calculations. A calculated field declares its dialect with the syntax property ("Tua"); when a request carries an inline semanticModel, Tua is the default. The Function reference below documents the full Tua function library.

Metadata in the model

Calculated fields can be defined at authoring time as part of the Semantic Data Model, so their formulas are reusable across queries. A calculated dimension is authored as a SemanticCalculatedDimension; a calculated measurement is authored as a SemanticCalculatedMeasurement. Each carries an apiName, a dataType, an expression, a syntax, and (for measurements) an aggregationType and a level. For the full authoring field schema, see Semantic Calculated Dimension and Semantic Calculated Measurement in the Authoring API.

A model-defined calculated measurement:

{
  "apiName": "AverageSumOfSales_clc",
  "label": "AverageSumOfSales",
  "dataType": "Number",
  "aggregationType": "UserAgg",
  "decimalPlace": 2,
  "expression": "WINDOW_AVG(SUM([Sales]), -3, 1)",
  "level": "TableCalc"
  // ...
}

A query references a model-defined calculated field by name with a semanticField expression. The referenced formula is resolved from the model:

{
  "fields": [
    {
      "expression": {
        "semanticField": {
          "name": "AverageSumOfSales_clc"
        }
      },
      "alias": "AverageSumOfSales_clc",
      "rowGrouping": false,
      "semanticAggregationMethod": "SEMANTIC_AGGREGATION_METHOD_USER_AGG"
    }
  ]
}

Query usage

Defining an on-the-fly calculated field

To compute a value without persisting it to the model, place a calculated_field object inside a fields[] entry's expression. A calculated_field has a name, an expression string, an optional syntax, and exactly one of calculated_measure_expression (for an aggregated numeric result) or calculated_dimension_expression (for a grouping value).

A calculated measurement declares its output type under calculated_measure_expression:

{
  "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"
    }
  ]
}

A calculated dimension declares its output type under calculated_dimension_expression, using dimension_output_type:

{
  "calculated_field": {
    "name": "simple-calc",
    "expression": "DATETIMEVALUE(DATE_TRUNC('day', ssot__Account__dlm.ssot__LastModifiedDate__c))",
    "calculated_dimension_expression": {
      "dimension_output_type": "SEMANTIC_DIMENSION_TYPE_DATE_TIME"
    }
  }
}

To group query rows by a computed dimension, set row_grouping to true on the field:

{
  "expression": {
    "calculated_field": {
      "name": "fiscal_quarter_on_the_fly_calculation",
      "expression": "CONCAT('FY', TEXT(FISCAL_QUARTER(AccountSemanticLayer__dll.LastModifiedDate__c)), '-Q', TEXT(FISCAL_YEAR(AccountSemanticLayer__dll.LastModifiedDate__c)))"
    }
  },
  "row_grouping": true
}

A measurement calculated field may also specify decimal places under decimal_places_options:

{
  "calculated_field": {
    "name": "table_calc_running_sum",
    "expression": "{ PARTITION [AccountSemanticLayer__dll].[Name__c]: { ORDERBY [AccountSemanticLayer__dll].[KQ_Id__c] : RANK(SUM([AccountSemanticLayer__dll].[AnnualRevenue__c])) } }",
    "calculated_measure_expression": {
      "measure_output_type": "SEMANTIC_MEASUREMENT_TYPE_NUMBER",
      "decimal_places_options": {
        "decimal_places": 2
      }
    },
    "syntax": "Tua"
  }
}

Operators and precedence

Expressions combine values with arithmetic, comparison, and logical operators. Operators evaluate in the following order, from highest to lowest precedence:

1. Parentheses ( )
2. Unary minus -
3. Exponentiation ^
4. Multiplication, division, modulo * / %
5. Addition, subtraction + -
6. Comparisons > < >= <= = !=
7. IN
8. NOT
9. AND
10. OR

Use parentheses to make grouping explicit. Arithmetic combines aggregates, fields, parameters, and literals — this on-the-fly measurement adds a parameter value to a summed field:

{
  "calculated_field": {
    "name": "summary-calc-in-select",
    "expression": "SUM([SemanticAccount__dlm].[sdm__AccountTypeId__c]) + [Parameters].[test12]",
    "calculated_measure_expression": {
      "measure_output_type": "SEMANTIC_MEASUREMENT_TYPE_NUMBER",
      "decimal_places_options": {
        "decimal_places": 2
      }
    }
  }
}

Arithmetic can also combine field references directly:

{
  "calculated_field": {
    "name": "nested_semantic_field_calc",
    "expression": "[logical_table].[AnnualRevenue] + [SemanticContact_SDO].[MailingLatitude]",
    "syntax": "Tua",
    "calculated_measure_expression": {
      "measure_output_type": "SEMANTIC_MEASUREMENT_TYPE_NUMBER"
    }
  }
}

Math and numeric functions

Numeric functions operate on numbers and numeric expressions. POWER(base, exponent) raises a number to a power:

{
  "calculated_field": {
    "name": "simple_power_calc",
    "expression": "POWER(2,2)",
    "calculated_measure_expression": {
      "measure_output_type": "SEMANTIC_MEASUREMENT_TYPE_NUMBER"
    }
  }
}

ROUND(value, places) rounds to a given number of decimal places, and ABS(value) returns absolute value. This model-defined table calculation uses ABS in a period-over-period growth formula:

{
  "apiName": "Calculation18",
  "dataType": "Number",
  "aggregationType": "UserAgg",
  "expression": "(ZN(SUM([Superstore_Orders].[Sales])) - LOOKUP(ZN(SUM([Superstore_Orders].[Sales])), -1)) / ABS(LOOKUP(ZN(SUM([Superstore_Orders].[Sales])), -1))",
  "level": "TableCalc"
}

String functions

String functions build and transform text. CONCAT(...) joins strings, and TEXT(value) converts a value to text — combined here to format a fiscal-period label:

{
  "calculated_field": {
    "name": "fiscal_quarter_on_the_fly_calculation",
    "expression": "CONCAT('FY', TEXT(FISCAL_QUARTER(AccountSemanticLayer__dll.LastModifiedDate__c)), '-Q', TEXT(FISCAL_YEAR(AccountSemanticLayer__dll.LastModifiedDate__c)))"
  }
}

LEFT(text, count) returns the leftmost characters. STR(value) converts a value to a string, and text values concatenate with +. This model-defined calculated dimension formats a rounded score with a text suffix:

{
  "apiName": "Avg_Quality_Score_clc",
  "dataType": "Text",
  "expression": "IF [Quality_Score_Color_clc] = \"High\"\nTHEN STR(ROUND([Average_Quality_Score_clc],2)) + \" (High)\"\nELSEIF [Quality_Score_Color_clc] = \"Medium\"\nTHEN STR(ROUND([Average_Quality_Score_clc],2)) + \" (Medium)\"\nELSEIF [Quality_Score_Color_clc] = \"Low\"\nTHEN STR(ROUND([Average_Quality_Score_clc],2)) + \" (Low)\"\nELSE STR(ROUND([Average_Quality_Score_clc],2)) + \" (Very Low)\"\nEND"
}

Date and datetime functions

Date functions extract, truncate, and construct date and datetime values. DATE_TRUNC(part, datetime) truncates a datetime to a given part, and DATETIMEVALUE(...) produces a datetime value:

{
  "calculated_field": {
    "name": "simple-calc",
    "expression": "DATETIMEVALUE(DATE_TRUNC('day', ssot__Account__dlm.ssot__LastModifiedDate__c))",
    "calculated_dimension_expression": {
      "dimension_output_type": "SEMANTIC_DIMENSION_TYPE_DATE_TIME"
    }
  }
}

DATEPART(part, date) returns a numeric component of a date, such as the year. This calculated dimension extracts the year from another calculated field:

{
  "calculated_field": {
    "name": "datepartyear:F1",
    "expression": "DATEPART('year', [ship_datetime_clc])",
    "calculated_dimension_expression": {
      "dimension_output_type": "SEMANTIC_DIMENSION_TYPE_NUMBER"
    },
    "syntax": "Tua"
  }
}

Fiscal date functions

Fiscal date functions return fiscal-calendar components. FISCAL_YEAR(date) returns the fiscal year and FISCAL_QUARTER(date) returns the fiscal quarter for a date:

{
  "calculated_field": {
    "name": "fiscal_quarter_on_the_fly_calculation",
    "expression": "CONCAT('FY', TEXT(FISCAL_QUARTER(AccountSemanticLayer__dll.LastModifiedDate__c)), '-Q', TEXT(FISCAL_YEAR(AccountSemanticLayer__dll.LastModifiedDate__c)))"
  }
}

Conditional and null-handling functions

Conditional expressions branch on a condition. The IF ... THEN ... ELSEIF ... ELSE ... END form returns different values per branch; IN tests membership in a value list. This model-defined measurement returns an amount only for matching forecast categories:

{
  "apiName": "Open_Pipeline_clc",
  "dataType": "Number",
  "aggregationType": "Sum",
  "expression": "IF [Opportunity26].[Opportunity_Forecast_Category110] IN (\"Pipeline\", \"Best Case\", \"Most Likely\", \"Commit\") \n    THEN [Opportunity26].[Total_Amount19]\nEND",
  "level": "Row"
}

IFNULL(value, replacement) substitutes a replacement when a value is null. This measurement sums two counts, treating null counts as zero:

{
  "apiName": "Test_clc",
  "dataType": "Number",
  "aggregationType": "UserAgg",
  "expression": "IFNULL(COUNT([Opportunity26].[Opportunity_Id23]),0)+IFNULL(COUNT([Product2].[Product_Id75]),0)",
  "level": "AggregateFunction"
}

ISNULL(expression) returns whether a value is null, and ZN(expression) converts a null numeric result to zero. The growth formula above uses ZN so that missing sales values participate in arithmetic as zero:

{
  "apiName": "Calculation18",
  "expression": "(ZN(SUM([Superstore_Orders].[Sales])) - LOOKUP(ZN(SUM([Superstore_Orders].[Sales])), -1)) / ABS(LOOKUP(ZN(SUM([Superstore_Orders].[Sales])), -1))",
  "aggregationType": "UserAgg",
  "level": "TableCalc"
  // ...
}

Aggregation functions

Aggregation functions collapse many rows into a single measurement value. Common ones include SUM, MIN, MAX, AVG, COUNT, COUNTD (distinct count), and MEDIAN — this is a representative sample, not the full list. For every supported aggregation function (including PERCENTILE, CORR, COVAR, COVARP, STDEV, STDEVP, VAR, and VARP), see Aggregation in the Function reference below. COUNTD counts distinct values of a field:

{
  "calculated_field": {
    "name": "countd",
    "expression": "COUNTD([SemanticAccount__dlm].[semantic__AccountSource__c])",
    "calculated_measure_expression": {
      "measure_output_type": "SEMANTIC_MEASUREMENT_TYPE_NUMBER"
    }
  }
}

When a calculated field wraps an aggregation, set the field's aggregation method to SEMANTIC_AGGREGATION_METHOD_USER_AGG so the expression's own aggregation is used rather than an outer one. Aggregate expressions also appear in Top-N criteria — here MEDIAN ranks dimension values:

{
  "operator": "AdvancedDimension",
  "top_bottom_criteria": {
    "top_bottom_limit": 10,
    "expression": "MEDIAN([superstore_fixed_nulls].[Quantity150])"
  }
}

Window and table-calculation functions

Table calculations compute across the ordered result set — running totals, moving averages, rankings, and offset lookups. A calculated measurement authored at level TableCalc carries a window function. WINDOW_AVG(expression, start, end) averages a measure over a sliding window of rows:

{
  "apiName": "AverageSumOfSales_clc",
  "dataType": "Number",
  "aggregationType": "UserAgg",
  "expression": "WINDOW_AVG(SUM([Sales]), -3, 1)",
  "level": "TableCalc"
  // ...
}

LOOKUP(expression, offset) returns a measure's value from a row offset by a given number of positions — negative offsets look backward. The period-over-period formula uses LOOKUP(..., -1) to reference the prior row:

{
  "apiName": "Calculation18",
  "expression": "(ZN(SUM([Superstore_Orders].[Sales])) - LOOKUP(ZN(SUM([Superstore_Orders].[Sales])), -1)) / ABS(LOOKUP(ZN(SUM([Superstore_Orders].[Sales])), -1))",
  "aggregationType": "UserAgg",
  "level": "TableCalc"
  // ...
}

Table calculations use a PARTITION/ORDERBY window frame. The frame partitions rows by one set of columns, orders within each partition, and applies an analytics function. RANK(measure) assigns a rank within the ordered partition:

{
  "calculated_field": {
    "name": "table_calc_running_sum",
    "expression": "{ PARTITION [AccountSemanticLayer__dll].[Name__c]: { ORDERBY [AccountSemanticLayer__dll].[KQ_Id__c] : RANK(SUM([AccountSemanticLayer__dll].[AnnualRevenue__c])) } }",
    "calculated_measure_expression": {
      "measure_output_type": "SEMANTIC_MEASUREMENT_TYPE_NUMBER",
      "decimal_places_options": { "decimal_places": 2 }
    },
    "syntax": "Tua"
  }
}

The ORDERBY clause accepts sort direction (ASC / DESC) and null placement (NULLS FIRST / NULLS LAST). Table-calculation expressions can also be used inside filters — this filter selects rows by an ordered index computed with an ORDERBY frame:

{
  "unified_filter": {
    "filter_logic": "1",
    "filters": [
      {
        "fields": [
          {
            "expression": "{ORDERBY [Superstore_Orders].[Region] ASC NULLS FIRST : ([Row_Index_1_clc])}"
          }
        ],
        "operator": "Between",
        "values": [1, 2],
        "include_nulls": false
      }
    ]
  }
}

Identity functions

Identity functions return values about the current user. USERID() returns the running user's ID; combine it with LEFT to compare on a 15-character ID prefix. This model-defined calculated dimension marks rows owned by the current user:

{
  "apiName": "FFact_owner_opp_history_lv_clc",
  "label": "FFact owner - opp history lv",
  "dataType": "Boolean",
  "expression": "LEFT([SW_FF_OPP_HIS_PA_lv].[Forecasting_Fact10_Owner], 15) = LEFT(USERID(), 15)"
}

Referencing parameters in formulas

A formula can reference a model parameter with bracket notation, as [Parameters].[<name>]. This on-the-fly measurement adds a parameter value to a summed field:

{
  "calculated_field": {
    "name": "summary-calc-in-select",
    "expression": "SUM([SemanticAccount__dlm].[sdm__AccountTypeId__c]) + [Parameters].[test12]",
    "calculated_measure_expression": {
      "measure_output_type": "SEMANTIC_MEASUREMENT_TYPE_NUMBER",
      "decimal_places_options": {
        "decimal_places": 2
      }
    }
  }
}

Comments in expressions

You can annotate expressions with comments. Line comments (//) and block comments (/* */) are ignored during evaluation.

Function reference

These are the built-in functions available in Tua formula expressions, as exposed in the Semantic Data Model authoring UI. Operators and precedence are covered in the Operators and precedence section above.

Functions differ in how they evaluate, which determines whether the calculated field is a dimension or a measurement:

• Row-level (scalar) — evaluates independently for each row and returns one value per row. Math, String, Date/Fiscal, Conditional & Null, and AI & Predictive functions are row-level, and typically define a calculated dimension.
• Aggregate — collapses the rows in a group into a single measurement value. When a calculated field wraps an aggregate expression, set its aggregation method to SEMANTIC_AGGREGATION_METHOD_USER_AGG so the expression's own aggregation is honored (see Limitations).
• Window (table calculation) — returns one value per row, computed over an ordered partition of neighboring rows. These require a PARTITION/ORDERBY frame.
• Level of detail — computes an aggregation at a dimensionality you specify, independent of the query's grouping.

Each category below notes which evaluation applies.

Math

Evaluation: Row-level. The exceptions are MAX and MIN: with two arguments they are row-level (the larger/smaller of the two values per row); applied to a single field they act as an aggregate over the group.

String

Evaluation: Row-level.

Date/Fiscal

Evaluation: Row-level.

Functions that take a date_part argument (such as DATEADD, DATEPART, DATETRUNC, DATEDIFF, and DATENAME) accept these values: year, quarter, month, week, day, dayofyear, weekday, hour, minute, second, milliseconds, iso-year, iso-quarter, iso-week, and iso-weekday. Fiscal-calendar variants — fiscal_year, fiscal_quarter, fiscal_month, and fiscal_week — are also accepted, and follow the model's fiscal-year configuration.

Conditional & Null

Evaluation: Row-level.

The IF and CASE functions support multi-line syntax with the keywords THEN, ELSE, ELSEIF (for IF), WHEN (for CASE), and END:

IF <test1> THEN <then1>
ELSEIF <test2> THEN <then2>
...
[ELSE <default>]
END

CASE <expression>
WHEN <value1> THEN <then1>
WHEN <value2> THEN <then2>
...
[ELSE <default>]
END

Aggregation

Evaluation: Aggregate. Each function collapses the rows in a group into a single measurement value. Set the calculated field's aggregation method to SEMANTIC_AGGREGATION_METHOD_USER_AGG when wrapping one of these (see Limitations).

Window & Table Calc

Evaluation: Window (table calculation). Each function returns one value per row, computed over an ordered partition; it requires a PARTITION/ORDERBY frame (or window bounds). A table calculation must act on an aggregate expression (for example SUM([Sales])) or wrap another table calculation — it cannot be applied directly to a row-level expression.

Wrap a table calculation in a PARTITION/ORDERBY block that defines the partition and the sort order it operates over:

{ PARTITION <dimension> [, <dimension> ...] :
  { ORDERBY <dimension> [ASC | DESC] [NULLS FIRST | NULLS LAST] [, ...] :
    <table calc function>(<aggregate expression>)
  }
}

{ PARTITION [AccountSemanticLayer__dll].[Name__c] :
  { ORDERBY [AccountSemanticLayer__dll].[KQ_Id__c] :
    RANK(SUM([AccountSemanticLayer__dll].[AnnualRevenue__c]))
  }
}

PARTITION/ORDERBY blocks can't be saved as a persistent calculated field in the model — pass them at query time on an on-the-fly calculated_field.

Like all table calculations, these functions act on an aggregate expression or wrap another table calculation — not a row-level expression.

TOTAL is grouped with table calculations because of its evaluation scope, but it behaves differently from the row-oriented functions above (such as RANK or RUNNING_SUM). Instead of computing per row, TOTAL re-runs the underlying aggregation at the grain defined by the PARTITION clause — conceptually similar to a Level of Detail expression, but scoped to the table calculation's partition rather than a fixed set of dimensions.

Level of Detail

Evaluation: Level of detail. Each expression computes an aggregation at the dimensionality you specify, independent of the query's grouping.

A FIXED LOD is computed before the query's dimension filters, so ordinary dimension filters do not reduce the rows it scans — they apply only to the outer query, not to the FIXED calculation. INCLUDE and EXCLUDE LODs are computed after dimension filters, so those filters do apply to their calculation. To constrain a FIXED calculation's scope, use a context filter, which applies before the FIXED LOD is computed.

AI & Predictive

Evaluation: Row-level.

Reference

For the full request schema, see Request Reference.

Limitations

• A calculated field is either a measurement or a dimension, not both. Supply exactly one of calculated_measure_expression or calculated_dimension_expression per calculated_field.
• Wrapped aggregations use USER_AGG. When an expression contains its own aggregation function (for example COUNTD(...) or a TableCalc-level formula), set the field's aggregation method to SEMANTIC_AGGREGATION_METHOD_USER_AGG so the expression's aggregation is honored.
• Table calculations require an ordering. Window and ranking functions evaluate over an ordered frame; a table-calculation formula specifies its PARTITION and ORDERBY (or window bounds) so row order is well-defined.
• Reference parameters with bracket notation. Reference a model parameter in a formula as [Parameters].[<name>].
• Calculated dimensions can only use FIXED LOD expressions. EXCLUDE and INCLUDE depend on the view's grouping, which isn't defined for a field that itself supplies grouping — use them only in calculated measurements.

Related

• Semantic Calculated Dimension — Authoring API for model-defined calculated dimensions.
• Semantic Calculated Measurement — Authoring API for model-defined calculated measurements.
• Filtering — Use calculated fields and table-calculation expressions within filters.
• Grouping — Group query rows by a calculated dimension with row_grouping.
• Aggregation & Totals — How aggregation methods apply to calculated measurements.
• Request Reference — Complete field schema for calculated fields and expressions.