I was trying to create a Named set in SSAS 2008 using the MDX calculations. I wanted to include all of the members of a hierarchy except one of them. I started with trying to use the filter expression and this worked but was very cumbersome to get the syntax right. I finally switched over to the MDX function “Except”.

To create a Named Set in Analysis Services Click on the Calculation tab and then the Named Set button across the top.

The Code in the Named Set was:

Except

(

{[Referral Type].[Referral Type Desc].Children},

{[Referral Type].[Referral Type Desc].&[ER TO IP]}

)

Here is an image of the Hierarchy. The arrow points to the member I want to remove from the Named Set.

To Test it I dragged over the hierarchy into the cube browser and dragged the named set up top. You can see the ER to IP member is missing.