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 Hierarchy | Date Week Hierarchy |
|---|---|
| Year | Year |
| Quarter | Week |
| Month | Day |
| 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
| Level | Query Name |
|---|---|
| Date Month Hierarchy/Year | YearMonth |
| Date Month Hierarchy/Day | DayMonth |
| Date Month Hierarchy/Year | YearWeek |
| Date Month Hierarchy/Day | DayWeek |
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.