Create a Calculated Member to remove need to load pre calculated measure

Question

I am trying to replicate the following sql statement into MDX so that I can create a calculated member in the cube using the base loaded members instead of having to calculate it outside the cube in the table and then loading it

SUM(CASE WHEN ((A.SALES_TYPE_CD = 1) AND (A.REG_SALES=0)) 
     THEN A.WIN_SALES
     ELSE 0 
END) AS Z_SALES

I am currently loading SALES_TYPE_CD as a dimension and REG_SALES and WIN_SALES as measures.
I also have a few other dimensions in the cube but for simplicity, lets just say I have 2 other dimensions, LOCATION and ITEM
The dimension has LOCATION has 3 levels, "Region"->"District"->"Store", ordered from top to bottom level.
The dimension has ITEM has 3 levels, "CLASS"->"SUBCLASS"->"SKU", ordered from top to bottom level. The dimension has SALES TYPE has 2 levels, "SALES_TYPE_GROUP"->"SALES_TYPE_CD", ordered from top to bottom level. I know that I cannot create a simple calculated member in the cube which crossjoins the "SALES_TYPE" dimension with another dimension to get the answer I want. I would think that it would be a more complicated MDX statement something like :

CREATE MEMBER CURRENTCUBE.[Measures].[Z_Sales]
 AS 'sum(filter(crossjoin(leaves(), [Sales Type].[Sales Type].
[Sales_Type_CD].&[1]), [Measures].[REG_SALES]=0),[Measures].
[WIN_SALES])', 
FORMAT_STRING = '#,#', 
VISIBLE = 1  ;

But this does not seem to return the desired result. What would be the proper MDX code to generate the desired result?

The REG_SALES=0 filter needs to be evaluated at the fact row grain right (rather than aggregating up to a product subtotal for a month then checking whether the subtotal is 0)? In that case if you will make REG_SALES a degenerate dimension you can do this efficiently in MDX. Is that a possibility? — GregGalloway
Yeah, I was gonna say, I know there's the FILTER() function, but you will make your life a lot easier if you add a dimension on Reg_Sales. — Tab Alleman

agm123 agm123 · Accepted Answer · 2016-04-03T04:28:58

I did a bunch of testing with the data and I now know that there is no way I can get the right answer by using MDX alone in this scenario. Like "Greg" and "Tab" suggested, the only way would be to have reg sales as a dimension. Since this is a measure, that is out of the question because of the large number of possibilities for the value which has a data type of decimal (18,2) Thanks for taking the time to answer the question.

Create a Calculated Member to remove need to load pre calculated measure

1 Answers