Functions such as NonEmpty(), Filter() and Exists() must use a numeric expression

Many of the MDX functions, such as NonEmpty(), Filter() and Exists(), seem to be easy enough to use, but they present quite a challenge for people with T-SQL background. The common theme among them is that they all claim to return dimension members, such as a list (or a set of tuples in MDX lingo) of countries, but they must use a fact measure (a numeric expression in another word) as the condition, such as the sales amount must be more than $10K. We are just not used to using fact measures.

Use Except() function to leave out a dimension member

In many situations, we just simply want to leave out a dimension member, such as one of the Product Categories, no matter what the sales amount is.

Run this simple query, and you will get the Order Quantity for all the Product Categories for each month.

SELECT [Date].[Month of Year].Children ON COLUMNS, [Product].[Product Categories].[All].Children ON ROWS FROM [Adventure Works] WHERE ([Measures].[Order Quantity])

What if I simply just want to leave out the Components in my query? Components are not the “final” products. Clothing, Bikes and Accessories are. So I have quite a good reason to leave it out of the query.

Now run this query with the help of the Except() function. Components is no longer in the result.

The Except() function does not involve any numeric expressions

Always wanting to publish novels, but having the fortune to work in the data warehouse corner of technology, Sherry Li started to write the mysteries of the Microsoft Business Intelligence. She writes everything from T-SQL to MDX, ETL to Expressions to Scripting, Reporting to Cubes. You can find her writings at bisherryli.wordpress.com.