Hi Guys,

We're having some problems with an MDX statement defining a calculated
member. What we have is a cube with 2 different measures. We would like to
determine which measure to use based on one of the dimensions (lets call it
Products) .

The products dimension has 3 levels. Where the member is a descendant of 1
of the members in the first level then we want to use measure1, otherwise use
measure2.

The MDX looks like this:

iif(Ancestor([Product].CurrentMember, [Product].[level1]) is
[Product].[Level1].&[9],

[Measures].[Measure1],
[Measures].[Measure2]
)

This all looks fine in a pivot table but when I put the dimension on rows
and select 1 or more, but not all of the members in the dimension, the grand
total for the measure is never correct. Usually it's whatever the grandtotal
is for measure1 or measure2. Can anybody shed any light on this for me?

What do you want the calculation to show when you're at the all member in
the product dimension? The way the calculation is set up, it will be
Measure2 - this your intent?

--
_______________
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.

"Rob Anderson" <Rob Anderson (AT) discussions (DOT) microsoft.com> wrote

Hi Richard, thanks for your response. I want the All level to show the
aggregation of the 2 measures. This would mean allocating the amount for
measure1 to all descendants of prduct.level1 and allocating the amount for
measure2 to all other members and then aggregating. Any ideas?

"Richard Tkachuk [MSFT]" wrote:

Something along the lines

iif(
Product.currentmember IS product.[All],
[measures].measures1 + [measures].measures2,
<your original expression>
)

--
_______________
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.


"Rob Anderson" <RobAnderson (AT) discussions (DOT) microsoft.com> wrote