Skip to main content

Add Calculations

You add a calculation to a model by writing an MDX formula that operates on other metrics in the model.

Calculation formulas are written in MDX syntax, but only support a small subset of MDX functions and operators. They can only operate on metrics currently 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.

note

AtScale recommends using Dimension Calculation Groups, which allows you to reduce the number of calculations in a model.

Before You Begin​

  • If you have experience with creating formulas in Tableau, be aware that calculation formulas in AtScale are somewhat different. For details, see Calculation Syntax.

  • The AtScale's MDX syntax validation mechanism detects cases where the equals operator is used incorrectly for comparing the CurrentMember function to a scalar value. It also displays a warning about using the [Dimension].[Hierarchy].[Level].CurrentMember syntax.

    If you need to disable these syntax checks, you can do so using the query.language.mdx.currentMember.allowLegacySyntax global setting. For details, see Configuring Global Settings.

  • When referencing objects in calculation formulas, you need to use their unique names (rather than their labels/display names).

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

Procedure​

To add a calculation:

  1. In the Repo Browser, click the new object button and select Calculation.yml.

  2. Enter a name for the calculation, then press Enter. The Calculation properties panel opens.

  3. Complete the following fields:

    • Display Name: The name of the calculation as it appears in AtScale.

    • Unique Name: The unique name of the calculation. This must be unique across all repositories and subrepositories.

    • Description: A description of the calculation.

    • MDX Aggregation Function: The aggregation function to use when this calculation is referenced via the Aggregate MDX function.

      note

      AtScale recommends selecting a value for this field if you plan on referencing this calculation from calculation groups. Otherwise, you may encounter errors at query time.

    • Visible in BI Tool: Determines whether the calculation appears in BI tools.

    • Formatting: The format for results of queries against the calculated metric. If you select Custom, you must also enter a custom format.

  4. In the Formula field, click the edit icon to open the Formula window, then enter the MDX formula you want to use.

    You can press Ctrl+Space while editing to toggle AtScale's MDX code completion feature; for more information, see MDX code completion below.

    When editing your formula, be aware of the following:

    • The formula must return a numeric value.
    • When using Power BI, AtScale recommends using the hierarchy name in the expression, rather than the dimension name.
    • You cannot combine metrics from multiple fact tables in the same formula if using CURRENTMEMBER.NAME in your formula.
    • You can only evaluate dimensions in a calculation formula with metrics from a single fact table.

  5. Click Test MDX Syntax to validate your formula.

  6. Click outside of the Formula window to close it.

  7. In the Calculation properties panel, click Apply to save your changes.

The new calculation appears in the calculations/ folder in the Repo Browser and can be added to models as needed.

note

If you want to specify a folder for the calculation for organizational purposes, you must do so from the Model properties panel.

MDX code completion​

When working in the Formula window, you can press Ctrl+Space to open AtScale's code completion feature. This lists the supported MDX functions, operators, statements, and objects that you can add to your formula.

image

Additionally, as you work, it displays contextual information about the specific function you're adding. You can click the blue icon at the bottom of the window to open the AtScale documentation about the function.

image

Additional information​