Formulas

Formulas are simple assignments of the form:

measure = expression

Measure can be any stored metrics measure (Opening, New, Expand, Churn, Closing, Notes). Virtual measures (Value) are not supported. Multiple assignments can be included in the same formula provided that each assignment targets a different measure.

Expression is a mathematical expression that describes how to calculate the measure. Standard operators and a set of scalar SQL functions are supported. Operands can be numbers, strings, member references, tuples, member property values and functions (see below).

Example:

[New] = [Net Income] * 0.15
[Closing] = [@Measure].[Opening] + ([Net Income], [@Measure].[New]) * 0.15

Operators

Type	Operators
Mathematical	+, -, *, /, %
Comparison	=, <>, <, >, <=, >=, like
Logical	and, or, not
Empty test	is null, is not null

Comparisons and logical operators produce a condition, which can be used inside iif() but cannot be the result of a formula. The first argument of iif() must be a condition: iif([A] > 0, …), not iif([A], …). Strings can be enclosed in single or double quotes: 'USD' or "USD". true and false are the numbers 1 and 0.

Empty Results

A formula that evaluates to null produces no value, exactly like a cell with no data:

• the cell shows as empty and is suppressed when empty rows/columns are excluded;
• if the cell holds a stored value, the formula empties it, and the value no longer adds up to the cell's parents;
• On Demand and Automatic formulas clear a stored value instead of leaving it in place.

Empty values propagate through expressions: if any operand of [A] + [B] is empty, the result is empty. Use coalesce() when a missing operand should count as zero, and the null keyword to return an empty result on purpose:

[New] = coalesce([A], 0) + coalesce([B], 0)
[New] = iif([Units] is null, null, [Sales] / nullif([Units], 0))

Member References

Dimension members can be referenced by name, key or tag using the syntax dim.name, dim.&key or dim.#tag. Identifiers that contain special characters or do not start with a letter/underscore must be enclosed in square brackets []. For legibility purposes it is recommended that dimension names/member names/etc are always entered between square brackets, e.g. [Account].[Rent]. An identifier that is not prefixed by a dimension name (e.g. [Opex]) is considered to be a member name in the same dimension as the member being calculated. Key or tag references must be prefixed with a dimension name -- [Period].&[202503] is valid, while &[202503] is not.

The measure pseudo-dimension must be referenced by role when used in expressions, i.e. [@Measure].[Closing]. On its own, [@Measure].[Opening] is the Opening value of the cell being calculated; inside a tuple it selects the measure of the tuple, e.g. ([Net Income], [@Measure].[New]).

Reading the measure being assigned ([New] = [@Measure].[New] * 1.1) returns the cell's current value: the stored value for a base cell, the aggregated value above it. Automatic formulas cannot do this: every write would trigger the formula again, with a new value each time.

Primary Hierarchy

All member references will considered to refer to the primary hierarchy of the dimension.

Tag References

Tag references in formulas must resolve to a single member. It is the responsibility of the user to ensure that.

Relative and Derived Member References

Besides fixed members, a formula can refer to members relative to the cell being calculated, or derive a member from another one:

Syntax	Meaning
[Dim].CurrentMember	The member of the cell being calculated
[Dim].KeyToMember(expr)	The member whose key is the value of expr (see below)
[Dim].StrToName(expr) / [Dim].StrToTag(expr)	The member whose name / tag is the value of expr (see below)
.Parent	The parent member
.Ancestor(n)	The ancestor n levels up
.PrevMember / .NextMember	The previous / next member on the same level
.Lag(n) / .Lead(n)	The member n places before / after on the same level

Methods can be chained and applied to any member: [Department].CurrentMember.Parent, [Period].CurrentMember.Lag(12), [Scenario].[Plan].Ancestor(1), [Period].KeyToMember(202503).PrevMember. n must be a whole number greater than or equal to 1.

Relative references are mostly used inside tuples, where each one replaces the cell's own member for its dimension:

-- Salaries of the parent department, one period earlier
[New] = ([Account].[Salaries], [Department].CurrentMember.Parent, [Period].CurrentMember.PrevMember)

-- Same period last year
[New] = ([Account].[Revenue], [Period].CurrentMember.Lag(12))

A member that does not exist (the parent of the top member, the period before the first one, a key that matches no member) makes the reference empty.

[Dim].CurrentMember on its own is the cell being calculated and is therefore rejected as an operand; inside a tuple it is allowed but has no effect.

KeyToMember

[Dim].KeyToMember(expr) resolves a member of Dim from a key computed at runtime. expr can use numbers, strings, functions and member property values of fixed members or of one other dimension's current member. It cannot read cell values.

The typical use is a property that stores a member key, e.g. an Entity's currency or a Scenario's last actual period:

-- Convert using the rate of the entity's own currency
[New] = [Local Amount] * ([Account].[FX Rate], [Currency].KeyToMember([Entity].CurrentMember.Property("Currency")))

-- Run-rate: the Actual value at the scenario's last actual period
[New] = ([Account].[Sales], [Scenario].[Actual], [Period].KeyToMember([Scenario].CurrentMember.Property("LastActualPeriod")))

[Dim].StrToName(expr) (also written StrToMember) and [Dim].StrToTag(expr) work the same way but match the member's name or tag instead of its key; StrToKey is the same as KeyToMember. Names are unique within a hierarchy; when several members carry a tag, the first one in dimension order is used.

-- The department named in the entity's "Cost Center" property
[New] = ([Account].[Salaries], [Department].StrToName([Entity].CurrentMember.Property("Cost Center")))

Member Properties

.Property("Name") returns the value of a dimension property for a member. It can be applied to the current member, a fixed member, or a member reached through the methods above:

[New] = [Sales] * [Department].CurrentMember.Property("Weight")
[New] = iif([Entity].CurrentMember.Property("Region") = 'EMEA', [Sales] * 0.2, [Sales] * 0.1)
[New] = iif([Period].CurrentMember.Lag(2).Property("StartDate") = '2025-01-01', 1, 0)

• Number properties can be used in arithmetic.
• Text and date properties can be compared, and passed to functions.
• A property that stores a member reference returns the member key: use it with KeyToMember(), or compare it with = / <>.
• A member without a value for the property gives an empty result.

Values are type-checked when the formula is compiled: arithmetic on text or dates, ordering comparisons (<, >, …) on member keys, and a text or date result for a numeric measure are errors. Text results are allowed for the Notes measure.

Tuples

Tuples are lists of member references, separated by comma and enclosed in parantheses: ([Account].[Rent], [Period].[Mar 2025]). A tuple cannot contain more than one member from the same dimension. Tuples can be used as operands in expressions. Tuple members can be fixed members or relative/derived member references (see above).

Functions

Formulas and calc scripts support the same set of Microsoft SQL scalar functions — mathematical, string, conditional, bitwise, date and conversion functions. See Scalar Functions for the list. Date functions work on date properties, e.g. year([Period].CurrentMember.Property("StartDate")), and convert / try_convert take a SQL type as their first argument: try_convert(decimal(18,2), [Entity].CurrentMember.Property("Rate")).

The result of a formula must be a number (or text, for the Notes measure): a formula returning a date or text, e.g. [New] = newid(), does not compile.

Important

Function arguments are not verified at compile time. Incorrect function calls in formulas might result in all queries failing for a tenant.

Below are some SQL functions commonly used to implement conditional logic:

Function	Description
iif(condition, true_value, false_value)	Evaluates condition and returns true_value if true or false_value if false
choose(index, val_1, val2, ...)	Returns the item at specified index from a list of values. index is 1-based. if index is lower than 1 or greater than the number of values, it returns NULL
greatest(expr_1, expr_2, ...)	Returns the maximum value from a list of expressions. At least one expression must be provided.
least(expr_1, expr_2, ...)	Returns the minimum value from a list of expressions. At least one expression must be provided.
coalesce(val_1, val_2, ...)	Returns the first non-NULL value in a list of values or NULL if all values are NULL At least one value must be provided.
isnull(val_1, val_2)	If val_1 is not NULL, return val_1, otherwise return val_2. Equivalent to coalesce(val_1, val_2) but faster.
nullif(val_1, val_2)	If val_1 is different from val_2, return val_1. If val_1 equals val_2 then return NULL. Generally used to prevent division by zero errors in expressions like A / nullif(B, 0)

Time Skip Functions

In addition to SQL functions, two Una-specific functions are implemented, lag() and lead() that are used to look up value for prior or future periods.

lag(expr, n) returns the value of expr for the period n periods before the current period; lead(expr, n) for the period n periods after. An n value of 1 means the prior (next) period. expr can be an expression, a member reference or a tuple that does not contain the time dimension. For instance, lag(([Account].[AR], [Department].[None]), 1) is valid, lag(([Account].[AR], [Period].[Mar 2025]), 1) is not valid. n must be an integer value greater or equal to 1.

lag() / lead() cannot be applied to an expression containing relative or KeyToMember member references. Put the shift on the time member instead: ([Account].[AR], [Department].CurrentMember.Parent, [Period].CurrentMember.Lag(1)).

Stored()

Stored() returns the value stored in the cell being calculated, before the formula is applied. It lets a formula respect values entered by users, for instance to calculate a value only where none was entered:

-- Keep the entered price; calculate it where none was entered
[New] = coalesce(Stored(), [Sales] / nullif([Units], 0))

Stored() is only available in Dynamic formulas and cannot be used inside lag() / lead() or KeyToMember(). Unlike [@Measure].[New], which above the base level reads the aggregated value, Stored() is always the value stored in the cell itself.

IsActual()

IsActual() returns 1 when the cell's period is an actual period for the cell's scenario, and 0 otherwise:

Scenario type	Result
Actual	1
Forecast	1 up to the scenario's Last Actual Period, 0 after it. When the scenario has no Last Actual Period, the last period flagged as actual in the time dimension is used
Plan, Benchmark	0

A summary period (quarter, year) is actual only when its last period is: with actuals through February, Q1 is not actual.

-- Actuals where available, plan otherwise
[New] = iif(IsActual() = 1, ([Scenario].[Actual], [Account].[Sales]), [Account].[Sales Plan])

IsActual() requires the model's Scenario dimension to be of type Scenario. It returns an empty value when the cell's scenario has no type (for instance the top Scenario member), so iif(IsActual() = 1, …) takes the false branch.

Comments

MDX/SQL style comments are supported:

• Single-line comments starting with --
• Multi-line comments enclosed between /* and */

Example

-- Calculate Gross Margin Percentage
[New] = ([Revenue] - [Cost of Goods Sold]) / nullif([Revenue], 0)

Substitutions/Parametrization

Not currently supported.

Scoping

Formulas can be scoped to execute for limited submodel slices instead of the entire model. Scope can be defined inside the formula editor, at the bottom of the screen. Scoping is not allowed on the target dimension or on Measures.

A scope consists of one or more members from various dimensions, plus a relationship: Self, SelfAndDescendants, Descendants, LeafDescendants. Multiple sets from the same dimension are disjunctive (additive), while sets from different dimensions are conjunctive (restrictive). Example:

• [Scenario].[Actual].Self
• [Scenario].[Plan].Self
• [Department].[Sales].LeafDescendants

Using the above scope, the calc will apply for all leaf members of the Sales department, for either Actual OR Plan scenarios.

important

Caution should be exercised when using non-leaf scopes.

Non-leaf Scoping

Data for non-leaf scopes will calculate, but may not aggregate as expected. For instance let's assume we calculate [Account].[Rent] for [Period].[Q1 2026] and we get a value of 10.

In a dynamic calc, if we don't have non-leaf data enabled for the [Period] dimension, when calculating the value for [Period].[FY 2026] the engine will only sum up [Jan 2026] through [Dec 2026] and will ignore [Q1 2026]. However, with non-leaf data enabled, [Q1 2026] will be added up to [FY 2026]. If [Q1 2026] was calculated to be the sum of [Jan 2026] to [Mar 2026] then doubling it up is probably not what was desired.

On Demand/Automatic formulas will calculate non-leaf data if so scoped, but will follow the data writing rules for the model. Default behavior for writing non-leaf data is to silently discard non-leaf data if the dimension doesn't support it.

Applies To: Aggregated Cells Only

Dynamic formulas have an Applies to setting, shown in the formula editor and in the Calculations list:

• All cells (the default) — the formula calculates every cell in scope.
• Aggregated cells only — the formula calculates only aggregated cells. Base cells keep the value that was entered or loaded, and aggregate normally.

A cell is aggregated when any of its members is: a non-leaf member (Q1, All Departments), the top member of a dimension that is not in the query, or a period that adds up several periods (Mar in a QTD or YTD view).

This is the way to calculate ratios above the input level. With Units and Sales entered at the lowest level:

-- Applies to: Aggregated cells only
[New] = [Sales] / nullif([Units], 0)

base cells show the entered price, while totals show the weighted average price instead of a sum of prices.

Aggregated cells only is not available for On Demand and Automatic formulas, which store results for base cells.

Execution

Formulas can be executed in different ways: Dynamic (on read), On Demand or Automatic (on write).

Dynamic Execution

Every time a query runs, the requested dimension members on all axes are examined to see if they are either calculated by formulas or one of their descendants is. Then all calculations in scope are executed before data is returned to the caller.

Aggregation during Dynamic Execution

Leaf calculated members are aggregated to their ancestors. Non-leaf calculated members are aggregated only if the hierarchy supports non-leaf data members. There is no guarantee as to the order of aggregation on diiferent dimensions.

Order of Calculations

Calculated members may depend on other calculated members. Calc formulas are applied in the order listed in Settings -> Calculations.

Performance Considerations

Because the result metadata is not known at query generation time, pretty much all dynamic calcs are included in every query. Calcs that are not in scope execute relatively fast, but every calc adds a small overhead. When creating dynamic calcs consider the impact on all queries against the model.

Relative member references and KeyToMember() inside tuples are resolved for each cell and add work to every query that uses the formula. Prefer fixed members where possible.

On Demand Execution

Formulas that are set to On Demand execution are assigned to a single model and will only update data in that model when executed. Each on demand formula is compiled to a SQL stored procedures with the same name as the fomula and placed in the [client] schema. The stored procedure receives the following arguments:

• username executing the calculation (required for the audit trail)
• for each dimension in the model, except the target dimension:
  • a scope member key and a relationship;
  • relationship can be one of: 0 (Self), 1 (SelfAndDescendants), 2 (Descendants), 3 (LeafDescendants)
  • both are optional, scope member defaults to 0, relationship defaults to 1 (SelfAndDescendants);
  • the above are used to restrict the execution of the formula to a scope;
  • execution is limited by the original scope of the formula intersected with the parameter scope
• a scope focus table (optional) which contains a list members for which the formula should be executed
  • this is an alternative way of providing a scope, typically used for automatic invocations
  • execution is limited by the original scope intersected with the scope focus provided in the table

Generated stored procs can be invoked directly from SQL, or run from the Calculations screen, a dashboard trigger or a workflow task, where the scope members and relationships are offered as the formula's parameters (see Running Calculations).

The formula is calculated for every base cell in scope. Cells where the result is empty are not written, and a stored value is cleared when the result is empty (see Empty Results).

Automatic Execution

Automatic formulas are akin to triggers in SQL. Automatic formulas are compiled to SQL stored procedures, just like On Demand formulas (see above) and can also be executed On Demand. However, whenever data is written to one of the dependencies of the formula, the store procedure is automatically triggered to update the value.

Example, let's assume the following formula for [Account].[Rent], scoped to [Scenario].[Plan], [Department].[None]:

[New] = ([Account].[Headcount], [@Measure].[Closing], [Department].[All]) * 120

Dependency model slice for this formula will be [Account].[Headcount] + [Scenario].[Plan] + [Department].[All].LeafDescedants. Any time data is written to a cell in this model slice, the stored proc will be invoked to update th value for [Account].[Rent]

Automatic formulas cannot use relative member references or KeyToMember() / StrToName() / StrToTag() inside tuples: the cells they read change from cell to cell, so a data write cannot be matched back to the formula.

Triggering Automatic Calculations

Execution of automatic formulas is handled by a background orchestrator process. Execution is asynchronous, with the scope of achieving eventual rather than immediate consistency. Data save operations may return before triggered calculations complete. This means that if we have [Account].[A] with [New] = [B] * 10 and we save a new value for [B] then immediately retrieve [A], the value for [A] might not be updated.

• Only one orchestrator instance per tenant runs at a time and it will keep running until all pending calculations are completed
• Data writes from a matrix and calc scripts executed from a workflow task will trigger the orchestrator
• Calc scripts invoked directly from SQL or custome calls to Appg_WriteM_xxx will add dependency calculations to the calculation queue, which will be processed when the orchestrator is triggered next
• Direct writes to the fact table will NOT add dependency calculations; this kind of actions must call any required calculations manually or peform them as part of the action
• ETL Data Imports do not currently add dependencies to the calculation queue, nor do they trigger the orchestrator. This behavior might change in the future.

Limitations

Omitted Dimensions

• Model queries may omit dimensions. Dynamic calcs that are scoped on any omitted dimension will not trigger.
• For an omitted dimension, CurrentMember is the top member of the dimension. IsActual() returns an empty value when the Scenario dimension is omitted.

Restrictions

• A formula cannot make references to itself (i.e. the member being calculated), not even when using lag()/lead(). To read the cell's own values, use [@Measure].[X] or Stored().
• Circular references between multiple formulas are not allowed
• A depth of at most 5 calculation dependencies is supported
• Calc scripts should not reference dynamically calculated formulas
• Do not write data to calculated members; data load processes
• Shared members should not be dynamically calculated
• Neither dynamically calculated members nor their ancestors should be shared (i.e. can not be the base member for a shared member)
• Dynamic calc formulas should not depend on shared members

danger

Results for any violation of the above restrictions are undefined. Any observed behavior is subject to change and cannot be relied upon.