Create a Named Set with all but one Member in MDX – SSAS

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:



{[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.


This entry was posted in mdx, SSAS, Syndication and tagged , . Bookmark the permalink.

One Response to Create a Named Set with all but one Member in MDX – SSAS

  1. Kenny says:

    Thanks for the helpful tip Mike. MDX is a very powerful language but it is easy to get confused on which approach to take.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s