Skip to main content
Version: I2023.2.0

Calculated measure syntax

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

Syntax specifics

Tableau users who are used to creating formulas in Tableau must be aware that the formulas for calculated measures in AtScale use the query name for dimensional hierarchies, level, and secondary attributes, not the object name. The object name is the name that appears in Tableau. The query 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.

Table 1. The levels in these two hierarchies

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 query names, as in this example.

Table 2. The query names for the levels that have the same names

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, double-click the object on the Cube Designer canvas and look for the Query Name field in the Edit dialog that appears.

More information

Add Calculated Measures