http://www.sqlservercentral.com/blogs/salvoz-sql/2013/01/21/re-writing-mdx-calculated-members-as-dax-measures/

Printed 2014/11/27 04:10AM

Re-writing MDX calculated members as DAX measures

By Jennifer Salvo, 2013/01/21

I have been working on re-creating some logic in a tabular cube that was originally implemented in multidimensional.  This involved creating some DAX measures that return the same results as the MDX calculated member counterparts in the multidimensional cube.  I thought that some individuals who are familiar with MDX in multidimensional cubes might find it interesting to see the equivalent DAX in the tabular cube.

The following tables will be used in the upcoming example code:

A relationship exists between the Sales fact and Sale Source dimension in both the multidimensional and tabular cubes.

The following calculated members exist in the multidimensional cube (MDX):

% Dexter Sales

IIF([Measures].[Sales Amount]=0, NULL, ([Measures].[Sales Amount],[Sale Source].[Point of Sale System].&[Dexter])/[Measures].[Sales Amount])

% e-Biz Sales

IIF([Measures].[Sales Amount]=0, NULL,((Measures].[Sales Amount],[Sale Source].[Order Type].&[Online]) + ([Measures].[Sales Amount],[Sale Source].[Order Type].&[Phone]))/[Measures].[Sales Amount])

% In-Store Ascend Sales

IIF(([Measures].[Sales Amount],[Sale Source].[Point of Sale System].&[Ascend])=0, NULL, ([Measures].[Sales Amount],[Sale Source].[Point of Sale System].&[Ascend],[Sale Source].[Order Type].&[In-Store])/([Measures].[Sales Amount],[Sale Source].[Point of Sale System].&[Ascend]))

The following are measures in the tabular cube (DAX) that produce the same results as the MDX calculated members:

% Dexter Sales

IF([Sales Amount]=0,BLANK(),CALCULATE([Sales Amount],’Sale Source’[Point of Sale System] = “Dexter”)/[Sales Amount])

% e-Biz Sales

IF([Sales Amount]=0,BLANK(),CALCULATE([Sales Amount],’Sale Source’[Order Type] = “Online” || ‘Sale Source’[Order Type] = “Phone”) / [Sales Amount])

% In-Store Ascend Sales

IF(CALCULATE([Sales Amount],’Sale Source’[Point of Sale System] = “Ascend”)=0,BLANK(),CALCULATE([Sales Amount],’Sale Source’[Point of Sale System] = “Ascend” && ‘Sale Source’[Order Type] = “In-Store”)/CALCULATE([Sales Amount],’Sale Source’[Point of Sale System] = “Ascend”))


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.