Skip to main content

Calculated metric syntax

Calculated metric formulas are written in MDX syntax, but only support a small subset of MDX functions and operators. They can only operate on the existing metrics in the model, and must return a numeric value. You can do simple math operations to combine metrics, or simple comparison operations to return a given metric value when certain conditions are met.

Syntax specifics

If you are used to creating formulas in Tableau, be aware that the formulas for calculated metrics in AtScale use the unique names for dimensional hierarchies, levels, and secondary attributes, not the label/display name. The label is the name that appears in Tableau. The unique name is the name that is used for the object in queries.

For example, a single Date dimension might have two hierarchies, Date Month Hierarchy and Date Week Hierarchy, which contain the following levels.

Date Month HierarchyDate Week Hierarchy
YearYear
QuarterWeek
MonthDay
Day

Although both hierarchies have levels named Year and Day, to prevent ambiguities in queries, the levels should have different unique names, as in this example.

LevelQuery Name
Date Month Hierarchy/YearYearMonth
Date Month Hierarchy/DayDayMonth
Date Month Hierarchy/YearYearWeek
Date Month Hierarchy/DayDayWeek

To see the query name for a hierarchy, level, or secondary attribute, open the Edit Dimension panel for its dimension and look for the object in the Hierarchies section. The names displayed there are the unique names for each object.