MDX for calculated member with two measures over time hierarchy

1
votes

I have a cube with two measures, say Measure A and Measure B. I need to create a single measure say C, from these two based on the business rule at any level of the hierarchy,

  1. If A is not empty at the current level (has value for all children at that level), then just aggregate A. Else, aggregate A wherever it is present and aggregate B wherever A is not present and then sum both to make C.

It goes like this

Quarter    Month    A    B
Q1         Apr      2    3
Q1         May           4
Q1         Jun           4

C should be 10 at Quarter level. Also, 2 for Apr, 4 for May and 4 for Jun [Month level]

I used the following MDX which works fine at Month level.

IIF(IsEmpty([Measures].[A]), [Measures].[B], [Measures].[A])

But, at quarter level it just gives me 2 instead of 10 which I now know why :) Any pointers on building a MDX to make it work at any level

[Year - Semester - Quarter - Month] (Granularity is at Month level only)

will be helpful. Thanks :)

1
sql-serverssasmdxbusiness-intelligence

1 Answers

0
votes

You can use this Mdx

Sum(
   Descendants('current member on your time hierarchy', 'month level'),
   CoalesceEmpty([Measures].[A], [Measures].[B])
)

CoalesceEmpty([Measures].[A], [Measures].[B]) is equivalent to IIf(IsEmpty([Measures].[A]), [Measures].[B], [Measures].[A])