Hello.
I have a problem with a Calculated Member based on a dimension
Property.
Cube Context: I have an Expenses Cube with a "Departments" dimension
and a "Account" dimension. Each department has several expenses
accounts but the accounts don't belong to the Department, so they're
two independent dimensions. Each account has a year budget, which I
include as a member property in the "Expensesdimension (because the
account budget is not part of the facts).
So in the Cube Editor I created a calculated member [AccBudget]like
this:

IIF(([Account].CurrentMember.Level.Name = "Account Number"),
StrToValue([Account].CurrentMember.Properties("Budget")),
null)

The problem is that when I browse the cube (be it with Excel or OWC),
and I put the "Departments" dimension and then the "Account" Dimension
(for example in the rows area), and I expand the "Departments"
dimension, it shows only the Accounts related to that Department (as it
should be), but in the moment that I add the [AccBudget] measure to the
table, the pivot table shows, for each Department, all of the Accounts
in the "Accounts" dimension, no matter if they're related to the
respective Department or not.

I don't know if this is the expected behavior, but it's definetly
counter intuitive. Thanks in advance for your help.

Since the value of [AccBudget] measure is soley based on the Account"
Dimension, it won't automatically be filtered by the "Non Empty" clause
applied by Excel or OWC.

Assuming that there is a base cube measure like [Amount], you can set
[AccBudget] to null when [Amount] is empty:

[Account].[Account Number] And
Not IsEmpty([Measures].[Amount]),
StrToValue([Account].CurrentMember.Properties("Budget")),
null)

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Thank you very much for your effective support and quick response.
That's exactly what I was trying to do, because when I added another
calculated member
[AvailableBud] as
[Measures].[AccBudget] - [Measures].[Amount]
it got more obvious that there were cells with an invalid or null [Amount]
measure, so I figured that I had to - somehow - filter those empty measures,
but didn't know how, and your suggestion works right in.

Thanks again

Rolando

"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote