Hello,

I am a first-time poster and new to MDX, thanks in advance for your
help.

I am using FoodMart 2000 Sales cube to try to create a level above
Product Family. That is, I would like to create a level called
"Consumable" that contains "Drink" and "Food" but not "Non-COnsumable."
I have used Except which returns the correct members but is it
possible to return one row called "Consumable" rather than two rows
("Food" and "Drink")?

Except
({[Product].[Product Family].members}
,{[Product].[Product Family].&[Non-Consumable]}
)

I assume a Named Set is the correct implementation, correct? Is there
a function to consolidate set members into a single level/member? Is
that the correct MDX terminology?

Regards,
Sean

The typical way to do this with Analysis Services 200 is to define an
Aggregate() calculated member over the selected subset. Such a member
may have some limitations (in AS 2000), like not working with Distinct
Count measures:

'Aggregate(Except([Product].[Product Family].members,
{[Product].[Product Family].&[Non-Consumable]}))'


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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