Skip to main content
Version: I2024.2.x

Add Calculated Measures

You can add a calculated measure to a cube by writing an MDX formula that operates on existing measures already defined in the cube.

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.

note

Note: AtScale recommends using Dimension Calculation Groups, which allows you to reduce the number of Calculated Measures in a model.

Prerequisites​

  • In case you have experience with creating formulas in Tableau, you need to consider that the formulas for calculated measures in AtScale are somewhat different. For details, see Calculated measure syntax.

  • Consider that starting with release 2022.2.0, the syntax validation mechanism used when entering the MDX formula detects cases where the equals operator is used incorrectly for comparing the CurrentMember function to a scalar value. Also, it displays a warning about using the [Dimension].[Hierarchy].[Level].CurrentMember syntax.

    In case you have calculated measures created in earlier releases and you need to turn off these syntax checks, you ca do this using the query.language.mdx.currentMember.allowLegacySyntax engine setting. For details, see Changing Engine Settings.

Procedure​

To add a calculated measure:

  1. Open the main cube canvas.
  2. Open the Measures, Properties, and Calculated Measures panels. This allows you to see the existing cube measures that you can operate on.
  3. You'll need to get a measure's Query Name if you plan to use it in a calculated measure formula. You can use the Properties panel to see the query name of a selected measure.
note

Note: If you plan to use the CURRENTMEMBER.NAME function in your formula, you will also need to get the dimension attribute's Query Name. Pay attention to role-playing dimensions, as their query names have a prefix that proceeds the attribute's query name.

  1. Click the plus icon in the Calculated Measures panel.
  2. Enter a user-friendly Display Name for the new measure.
  3. (Optional) Edit the Query Name for the measure. This name should not be changed after the cube is published.
  4. (Optional) Select the Formatting for the values. See Formats for Data Values for a description of the available formats.
  5. In the MDX Aggregation Function field, select the aggregation function to use when this calculated measure is referenced via the Aggregate MDX function.
note

Note: AtScale recommends setting this field to a value other than None if you plan on referencing this calculated measure from calculation groups. Otherwise, you may encounter errors at query time.

  1. (Optional) Enter the name of a Folder that this measure should go in.

  2. Enter an MDX formula:

    • The dialog suggests attributes, measures, and functions that match the spelling of the text that you type. Click on a suggestion to accept it. You can also search for the names of attributes and measures that you can use, as well as scroll through the list of MDX functions and operators that AtScale supports.
    • The formula must return a numeric value.
    • When using Power BI, instead of the dimension name it is recommended to use the hierarchy name in the expression.
    • Note that you cannot combine measures from multiple fact tables in the same formula if using CURRENTMEMBER.NAME in your formula. You can only evaluate dimensions in a calculated measure formula with measures from a single fact table.

  3. Click Save.

MDX reference​

For details about the MDX functions and operators allowed in a calculated measure formula, see MDX Reference.