TechNet
Export (0) Print
Expand All

Building Measures in MDX

 

Applies To: SQL Server 2016

In Multidimensional Expressions (MDX), a measure is a named DAX expression that is resolved by calculating the expression to return a value in a Tabular Model. This innocuous definition covers an incredible amount of ground. The ability to construct and use measures in an MDX query provides a great deal of manipulation capability for tabular data.

System_CAPS_ICON_warning.jpg Warning


Measures can only be defined in tabular models; if your database is set in multidimensional mode, creating a measure will generate an error

To create a measure 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 measure within an MDX SELECT statement. Using this approach, the calculated member created by using the WITH keyword can be changed without disturbing the SELECT statement. However, in MDX you reference the measure in a different way than when in DAX expressions; to reference the measure you name it as a member of the [Measures] dimension, see the following MDX example:

with measure  'Sales Territory'[Total Sales Amount] = SUM('Internet Sales'[Sales Amount]) + SUM('Reseller Sales'[Sales Amount])  
select measures.[Total Sales Amount] on columns  
     ,NON EMPTY [Date].[Calendar Year].children on rows  
from [Model]  
  

It will return the following data when executed:

Total Sales Amount
200111331808.96
200230674773.18
200341993729.72
200425808962.34

CREATE MEMBER Statement (MDX)
MDX Function Reference (MDX)
SELECT Statement (MDX)

Community Additions

ADD
Show:
© 2016 Microsoft