I have a calculated measure (C) that is the ratio of two sparsely-populated
measures (A and B). For dimension members in which A and B don't exist, I
still get the calculated measure C. This means that a lot of dimension
members show up that have no values for A and B whenever I add measure C to
the pivot table.

How can I suppress C when there are no A and B measures? I tried a NON
EMPTY statement in the calculated measure but can't get the syntax right.

Thanks.

Jon

jpearce (AT) remove_this_dgapartners (DOT) com

Notice the calc members have a property "non empty behavoir". For calc C set
this property to A or B.

tom @ the domain below
www.tomchester.net


"Jon Pearce" <jon (AT) remove_this (DOT) pearcefamily.org> wrote

"Tom Chester" <publicNOSPAM (AT) tomchester (DOT) net> wrote

However, remember that "Non Empty Behavior" is just a performance hint. It
cannot overwrite results of the calculation.
Therefore if you want that the result of A/B was NULL when both A and B
NULLs, you need to code it as

iif( IsEmpty(B) AND IsEmpty(A), NULL, A/B )

and then set Non Empty Behavior to A.

--
==================================================
Mosha Pasumansky - www.mosha.com/msolap
Development Lead in the Analysis Server team
All you need is love (John Lennon)
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.
==================================================

Works great - thanks to both of you.

BTW - Fast Track to MDX is great - thanks, Mosha

Jon


"Mosha Pasumansky [MS]" <moshap (AT) microsoft (DOT) com> wrote