overwrite context dimension on calculated member (SSAS)

Question

Let's supose a simple scenario: a fact table F with two dimensions D1 and D2.

F   D1   D2
10   A   B
15   B   C

In this scenario I define a new calculated member C1 using an expression close than this one:

with member measures.C1 as
sum(
   descendants( [D1].[Ds].currentMember, , leaves ),
   [myMeasure]
)
select 
 measures.C1 on 0,
[D2].[Ds].AllMembers on 1
   from [MyCube]

How can I modify C1 to incorpore all time all D2 members in expression?

I get this results:

C1  D2
10  B
15  C

and I'm looking for this:

C1  D2
35  B
35  C

(of course this is a simplification of real problem, please, don't try to fix C1 expression, only add code to get expected results, I have tried with:

sum(
   { descendants( [D1].[Ds].currentMember, , leaves ),
     [D2].[Ds].AllMembers },
   [myMeasure]

unsuccesfully)

regards.

I am not sure I understand your desired output. Can you explain why 35 B would be shown twice? — Rick

Rick Rick · Accepted Answer · 2012-05-09T18:52:21

For this specific example, change your member statement the following.

WITH MEMBER [Measures].[C1] AS
SUM([D1].[Ds].[All], [myMeasure])

This gives you everything in that dimension for your measure. That value then should be repeated for each attribute in your D2 dimension.

Based on the title of the question and some of your text this is only a small example. It maybe possible that you need to investigate scope. It is pretty powerful and you can do some neat things with it.

overwrite context dimension on calculated member (SSAS)

1 Answers