Have you ever wondered how can I return all my non selling products in MDX? Well, if you found your way to this post hopefully you have! Fortunately this is actually quite easy to do.

ISEMPTY FUNCTION

The ISEMPTY function returns a BOOLEAN value. If the cell evaluated is empty the value of TRUE is returned and if it is not empty then the value of FALSE is returned. This is a great function to nest inside other functions. Let me show you how we can use this function along with the filter function in MDX.

MDX FILTER FUNCTION

Here is a simple MDX query that returns [Internet Sales] on columns and [Products] on rows.

In this query we can quickly see we definitely have products that have never had internet sales. Now how can we return only the products that have never had sales? Well there are many ways to do this of course but I want to show how to use the Filter Function along with the ISEMPTY function for this example.

Here I use the set of members returned from [Product].[Product].Children as the first parameter in the filter function. For the second parameter in the filter function I use the ISEMPTY function on [Measure].[Internet Sales Amount]. This means that only products from parameter 1 that have no sales will be returned in the final result set. In the result set above we can see that only products without sales are being returned in the final result set. Easy right?  

As always thanks for looking!