SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Salvo(z) SQL

Adam and Jennifer Salvo are IT professionals with over 10 years of diverse experience. Jennifer is a Business Intelligence developer focusing on the Microsoft BI stack (SSIS, SSAS and SSRS). Her prior work experience includes software development, systems analysis, end-user support, training, and SharePoint administration. Adam is a .NET technical lead with a current emphasis on Dev Ops and Windows Azure. His prior work experience includes .NET development, SQL Server administration, and BizTalk development. They also maintain a personal blog at salvoz.com.

Re-writing MDX calculated members as DAX measures

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


Leave a comment on the original post [salvoz.com, opens in a new window]

Loading comments...