MDX Calculated Members - Query-Scoped Calculated Members

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

If a calculated member is only required for a single Multidimensional Expressions (MDX) query, you can define that calculated member by using the WITH keyword. A calculated member that is created by using the WITH keyword no longer exists after the query has finished running.

As discussed in this topic, the syntax of the WITH keyword is quite flexible, even allowing a calculated member to be based on another calculated member.

WITH Keyword Syntax

Use the following syntax to add the WITH keyword to an MDX SELECT statement:

[ WITH <SELECT WITH clause> [ , <SELECT WITH clause> ... ] ] SELECT [ * | ( <SELECT query axis clause> [ , <SELECT query axis clause> ... ] ) ]FROM <SELECT subcube clause> [ <SELECT slicer axis clause> ][ <SELECT cell property list clause> ]  
<SELECT WITH clause> ::=  
   ( [ CALCULATED ] MEMBER <CREATE MEMBER body clause>) | <CREATE MEMBER body clause> ::= Member_Identifier AS 'MDX_Expression'  
   [ <CREATE MEMBER property clause> [ , <CREATE MEMBER property clause> ... ] ]  
<CREATE MEMBER property clause> ::=  
   ( MemberProperty_Identifier = Scalar_Expression )  
  

In the syntax for the WITH keyword, the Member_Identifier value is the fully qualified name of the calculated member. This fully qualified name includes the dimension or level to which the calculated member is associated. The MDX_Expression value returns the value of the calculated member after the expression value has been evaluated. The values of intrinsic cell properties for a calculated member can be optionally specified by supplying the name of the cell property in the MemberProperty_Identifier value and the value of the cell property in the Scalar_Expression value.

WITH Keyword Examples

The following MDX query defines a calculated member, [Measures].[Special Discount], calculating a special discount based on the original discount amount.

WITH   
   MEMBER [Measures].[Special Discount] AS  
   [Measures].[Discount Amount] * 1.5  
SELECT   
   [Measures].[Special Discount] on COLUMNS,  
   NON EMPTY [Product].[Product].MEMBERS  ON Rows  
FROM [Adventure Works]  
WHERE [Product].[Category].[Bikes]  

You can also create calculated members at any point within a hierarchy. For example, the following MDX query defines the [BigSeller] calculated member for a hypothetical Sales cube. This calculated member determines whether a specified store has at least 100.00 in unit sales for beer and wine. However, the query creates the [BigSeller] calculated member not as a child member of the [Product] dimension, but instead as a child member of the [Beer and Wine] member.

WITH   
   MEMBER [Product].[Beer and Wine].[BigSeller] AS  
  IIf([Product].[Beer and Wine] > 100, "Yes","No")  
SELECT  
   {[Product].[BigSeller]} ON COLUMNS,  
   Store.[Store Name].Members ON ROWS  
FROM Sales  
  

Calculated members do not have to depend only on existing members in a cube. Calculated member can also be based on other calculated members defined in the same MDX expression. For example, the following MDX query uses the value created in the first calculated member, [Measures].[Special Discount], to generate the value of the second calculated member, [Measures].[Special Discounted Amount].

WITH   
   MEMBER [Measures].[Special Discount] AS  
   [Measures].[Discount Percentage] * 1.5,   
   FORMAT_STRING = 'Percent'  
  
   MEMBER [Measures].[Special Discounted Amount] AS  
   [Measures].[Reseller Average Unit Price] * [Measures].[Special Discount],   
   FORMAT_STRING = 'Currency'  
  
SELECT   
   {[Measures].[Special Discount], [Measures].[Special Discounted Amount]} on COLUMNS,  
   NON EMPTY [Product].[Product].MEMBERS  ON Rows  
FROM [Adventure Works]  
WHERE [Product].[Category].[Bikes]  
  

See Also

MDX Function Reference (MDX)
SELECT Statement (MDX)
Creating Session-Scoped Calculated Members (MDX)