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:

• Sales Fact Table
• Sale Source Dimension Table
  • Stores data related to the type of sale.  This includes information such as did the sale occur online or in-store or what point of sale system captured the sale?
  • Attributes: Order Type (In-Store, Online, Phone), Point of Sale System (Ascend, Dexter)

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”))