MDX ISEMPTY function to return products with no sales.

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.

image

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.

image

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? Smile 

As always thanks for looking!

MDX NON EMPTY KEYWORD VS NONEMPTY FUNCTION

Non Empty vs NonEmpty

Hey everyone, in this blog I want to address a very common MDX Question. What is the difference between the NON EMPTY keyword and NONEMPTY function? To take it a step further which one should you use?

Non Empty keyword VS NONEMPTY Function.

The big difference between the NON EMPTY keyword and the NONEMPTY function is when the evaluation occurs in the MDX. The NON EMPTY keyword is the last thing that is evaluated, in other words after all axes have been evaluated then the NON EMPTY keyword is executed to remove any empty space from the final result set. The NONEMPTY function is evaluated when the specific axis is evaluated.

Should I use NON EMPTY keyword or NONEMPTY function?

Ok Mitchell, so you told me when each of these are evaluated but really you haven’t told me anything up until this point. Can you tell me which one I should use already? Well, unfortunately, it depends. Let’s walk through an example of each using the BOTTOMCOUNT function.

BOTTOMCOUNT FUNCTION with NON EMPTY Keyword

In this example I’m returning the bottom ten selling products for internet sales. Notice that I have returned all products that have no internet sales, this is not necessarily a bad thing, maybe you want to return products that don’t have sales.

image

However if you don’t want to return these products then we can try using the NON EMPTY keyword. In the below example you can see the results when I add NON EMPTY to the ROWS axis.

image

WHOOOAAA, what happened?? A lot of people would have expected the results here to show the bottom ten products that DID have sales. However, that is not the case, remember that I said the NON EMPTY keyword is evaluated LAST after all axes have been evaluated. This means that first the bottom ten selling products which have $0 in sales are first returned and then the NON EMPTY keyword removes all that empty space from the final result.

BOTTOMCOUNT function with NONEMPTY function.

So let’s try this again, if you want to return the bottom ten products that had sales then we must first remove the empty space before using the BottomCount function. Take a look at the code below:

image

In this code we first remove the empty space before using the BOTTOMCOUNT function. The result is we return the bottom ten products that had internet sales. Once again neither one is right or wrong here it just depends on what you want in your final result.

NON EMPTY Keyword vs. NONEMPTY Function – Performance

There is a very common misconception that the NONEMPTY function always performs better than the NON EMPTY keyword, however this is not true. My good friend Dustin Ryan has a great blog discussing this point. Head on over to his blog here: SQLDusty

Thanks for taking a look at this blog and I hope you enjoyed it!