Table of contents

Need to remove employees who have zero sales amount

I recently helped a co-worker with a simple MDX query. All she wanted to do is to remove employees from the return set who has no sales or who has zero $ sales. She was using the NON EMPTY keyword on both the X and Y axis. That removed employees who have no sales, but the employees who have zero sales amount are still showing in the data set.

A quick and simple way to remove those employees with zero sales amount is to create a calculated measure in the query. This new calculated measure will simply use the IIF function to turn the sales amount into a NULL value, when the sales amount is zero. The NON EMPTY keyword on the Y axis will automatically take care of the removal of those employees with zero sales amount.

Create a calculated measure with the IIF() function

Suppose that this is our initial MDX query, where all employees (at leaf level of the Employees hierarchy) who have sales are returned.

Now you would like to only see employees who have more than $100,000 sales. In the following modified MDX query, I added this calculated measure. 

[Measures].[Reseller Sales Amount > 100,000]

This measure is simply identical to [Measures].[Reseller Sales Amount], except that it is set by the IIF function to be NULL, when it is less than 100,000.

The NON EMPTY keyword will take care of the removal

The NON EMPTY keyword on the Y axis will automatically take care of the removal of those employees with sales less than 100,000.

Ordering results with both numeric and string expression

To be sure that we get only the employee who have more than 100,00 sales amount, we can sort the results by the sales amount using the order() function with this numeric expression.

[Measures].[Reseller Sales Amount > 100,000]

If you insist to sort the results by the employees’ name, use the employee names as a string expression in the order() function.

[Measures].[Employee name]

IIF() statement VS. Scope() statement in MDX Cookbook 2012

In the book MDX Cookbook 2012, we have quite extensive examples on how to use the query-based IIF() statement, as well as the the cube-based Scope() statement.

You can check out the recipe Detecting a particular member of a hierarchy while you are using the book.