MDX Calculated Dimension Member base on another dimension

Question

There are two dimensions

[record].[record id]

[product].[product name]

[measures].[sales]

I want to create a new dimension attribute, in the record dimension.

with member [record].[derived record id] as iif([product].[product name].[product name] 
in ('pen', 'eraser', 'notepad'), 0, [record].[record id].[record id])
select [measures].[sales] on 0, 
[record].[derived record id].[derived record id] on 1

You see what I am trying to do is to combine some the the rows where product name is in a select list and leave the rest as is. I can do this during the load process to create this new attribute, but how do I do it in MDX?

Thanks in advance.

FrankPl FrankPl · Accepted Answer · 2014-02-13T21:20:09

As far as I understand, you want to have a calculated member of the [record].[record id] hierarchy that delivers the value 0 for all measures in the cube in case the current product name member is one of 'pen', 'eraser', and 'notepad'. To achieve this, you can use the following MDX:

with member [record].[record id].[derived record id] -- note: you must name the hierarchy for calculated members
            as
            IIf([product].[product name].CurrentMember IS [product].[product name].[pen]
                or
                [product].[product name].CurrentMember IS [product].[product name].[eraser]
                or
                [product].[product name].CurrentMember IS [product].[product name].[notepad]
                ,
                0
                ,
                Measures.CurrentMember
               )
select [measures].[sales]
       on 0, 
       [record].[record id].[derived record id]
       on 1
  from [YourCube]

MDX Calculated Dimension Member base on another dimension

1 Answers