We use NON EMPTY key word on the rows axis to remove rows with NULL values. It works only when all the measures are NULL. If any one of the measures has value, NON EMPTY key word will not be able to remove rows.

NON EMPTY on rows axis worked as expected

A co-worker recently worked on getting 30+ measures from multiple measure groups out from our production cube. The NON EMPTY key word on the rows axis worked as expected.

SELECT
{ [Measures].[measure 1],
....
{[Measures].[measure 30] } ON COLUMNS,
NON EMPTY
[Employee].[Employee Name].[Employee Name].MEMBERS
ON ROWS
FROM
[REPORTING_CUBE]
WHERE
( [Date].[Date View].[Month].&[201601])

The month on the WHERE clause is hardcoded in this blog, but this query is supposed to be executed in an OPENQUERTY with a dynamic month value. In order to attach a period key value to the month, my co-worker decided to also get the month period key in the same query. (Let’s put aside why the OPENQUERTY is used and whether the month period key is needed or not and let’s focus on the NON EMPTY.)

NON EMPTY stopped working when a calculated Period Key is added

In order to get the hidden month period key, he used the WITH MEMBER to create a query-scope measure:

WITH MEMBER [Measures].[Period Key] AS LEFT([Date].[Date View].CurrentMember.Member_Key, 6)

This [Measures].[Period Key] is then added to the columns axis.

SELECT
{ [Measures].[Period Key],
[Measures].[measure 1],
....
{[Measures].[measure 30] } ON COLUMNS,

The query at the point returns rows that more than doubled what it returned before with many of them showing all measures NULL, except the Period Key.

The NON EMPTY is obviously not working at this point. When you think about it, this actually makes sense. In the context of the query, the Period Key has value and should be the same for all the employees whether those measures are NULL or not. This explains why NON EMPTY cannot remove the rows even when all the measures are NULL, since [Measures].[Period Key] has value.

Below is a made-up example. It shows that we get rows back even when all measures are NULL.

NONEMPTY() function is a lot more flexible

The NONEMPTY() function is a lot more flexible and should work in this case. But the usual form of it, where one measure is given as the second parameter, would not work in this case.

NONEMPTY(
[Employee].[Employee Name].[Employee Name].MEMBERS,
[Measures].[measure 1]
)

Use a named set as the second parameter for the NONEMPTY() function

We need to check for all the 30+ measures. The solution here is to use a named set as the second parameter.

NONEMPTY(
[Employee].[Employee Name].[Employee Name].MEMBERS,
[The Set]
)

We will define the named set as the following to combine all 30+ measure together into a set:

WITH
SET [The Set] AS
{ [Measures].[measure 1],
...
[Measures].[measure 30] }

Putting all together, here is the final query that will only return the employees who have values in those 30+ measures.

WITH
SET [The Set] AS
{ [Measures].[measure 1],
...
[Measures].[measure 30] }
SELECT
{ [Measures].[Period Key],
[The Set] } ON COLUMNS,
NONEMPTY(
[Employee].[Employee Name].[Employee Name].MEMBERS,
[The Set]
) ON ROWS
FROM
[REPORTING_CUBE]
WHERE
( [Date].[Date View].[Month].&[201601])

In my made-up example, only the two rows that are in red and bold will be returned, Baker, B and Brooks, W.

Always provide set_expression2 in NonEmpty() function

On MSDN the NonEmpty() Function page shows the syntax as this:

NONEMPTY(set_expression1 [,set_expression2])

It implies that the second parameter is optional. Check out my old blog to understand why we should always provide set_expression2 in the NonEmpty() function.

http://bisherryli.com/2012/01/20/mdx-1-why-should-we-always-provide-set_expression2-in-nonempty-function/