MDX Cell Calculations - Build Cell Calculations

Applies to: SQL Server Analysis Services Azure Analysis Services Fabric/Power BI Premium

Multidimensional Expressions (MDX) provides you with a number of tools for generating calculated values, such as calculated members, custom rollups, and custom members. However, using these features to affect a specific set of cells, or a single cell for that matter, would be difficult.

To generated calculated values for specifically for cells, you need to use the calculated cells feature in MDX. Calculated cells let you define a specific slice of cells, called a calculation subcube, and apply a formula to each and every cell within the calculation subcube, subject to an optional condition that can be applied to each cell.

Calculated cells also offer complex functionality, such as goal-seeking formulas, as used in KPIs, or speculative analysis formulas. This level of functionality comes from the pass order feature in Microsoft SQL Server SQL Server Analysis Services that allows recursive passes to be made with calculated cells, with calculation formulas applied at specific passes in the pass order. For more information on pass order, see Understanding Pass Order and Solve Order (MDX).

In terms of creation scope, calculated cells are similar to both named sets and calculated members in that calculated cells can be temporarily created for the lifetime of either a session or a single query, or made globally available as part of a cube:

• Query-scoped To create a calculated cell that is defined as part of an MDX query, and therefore whose scope is limited to the query, you use the WITH keyword. You can then use the calculated cell within an MDX SELECT statement. Using this approach, the calculated cell created by using the WITH keyword can be changed without disturbing the SELECT statement.

  For more information about how to use the WITH keyword to create calculated members, see Creating Query-Scoped Cell Calculations (MDX).

• Session-scoped To create a calculated member whose scope is wider than the context of the query, that is, whose scope is the lifetime of the MDX session, you use either the CREATE CELL CALCULATION or ALTER CUBE statement.

  For more information about how to use either the CREATE CELL CALCULATION or ALTER CUBE statement to create calculated cells in a session, see Creating Session-Scoped Calculated Cells

See Also

ALTER CUBE Statement (MDX)
CREATE CELL CALCULATION Statement (MDX)
Creating Query-Scoped Cell Calculations (MDX)
MDX Query Fundamentals (Analysis Services)