I've been working in the OLAP field for a while but I'm fairly new to
Analysis Services and my knowledge of MDX leaves something to be desired.
With this in mind, here's my question:

Is it possible to create a calculated member whose value changes depending
on a member property of a different dimension?


For example:

Suppose I have a (parent-child) Accounts dimension with a member property
'IsUnit' (values Y/N). My fact table contains both cash values and unit
values. Suppose I also have a 'Scenario' dimension with members 'Cash' and
'Units' which seperates Cash and Unit values for reporting purposes.

A particular account can have both cash and unit values in the fact table,
cash will be keyed with the Cash scenario, unit values with the Units
scenario.

Now suppose I want to combine the cash and units scenarios into a combined
'Mixed' scenario which displays the unit value for certain unit accounts and
the cash value for other, non-unit, accounts. The type of account is defined
by the 'IsUnit' member property in the Accounts dimension. I want a
calculated member 'Mixed' in the Scenario dimension with this behaviour:

Where account member property 'IsUnit'='Y', Mixed value= Units value
Where account member property 'IsUnit'='N', Mixed value= Cash value

Example fact data:

Account Scenario Value
-------------------------------
Cola Cash 10000
Cola Units 5000
Juice Cash 5400
Juice Units 270
Gross Profit Cash 4500000
Gross Profit Units 123500

Account table data:

Account IsUnit
---------------------
Cola Y
Juice Y
Gross Profit N

Values of Mixed scenario:
Cola 5000 (Unit value)
Juice 270 (Unit value)
Gross Profit 4500000 (Cash value)


Any suggestions on how to achieve this would be very gratefully received!

Thank-you in advance,
James Taylor

You can define a calculated member of the Scenario
dimension:

iif([Accounts].currentmember.properties('isunit')='Y',
[scenario].[units],[scenario].[cash])

(Syntax not checked)

HTH,
Brian
www.geocities.com/brianaltmann/olap.html

That is a beautiful thing.

Thank you.