Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
NonEmpty
Avg
IIf
Lag
Max
Min
Mtd
Qtd
Sum
Var
Wtd
Ytd
Collapse the table of content
Expand the table of content

NonEmpty (MDX)

Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).

Returns the set of tuples that are not empty from a specified set, based on the cross product of the specified set with a second set.

NONEMPTY(set_expression1 [,set_expression2])

set_expression1

A valid Multidimensional Expressions (MDX) expression that returns a set.

set_expression2

A valid Multidimensional Expressions (MDX) expression that returns a set.

This function returns the tuples in the first specified set that are non-empty when evaluated across the tuples in the second set. The NonEmpty function takes into account calculations and preserves duplicate tuples. If a second set is not provided, the expression is evaluated in the context of the current coordinates of the members of the attribute hierarchies and the measures in the cube.

Note Note

Use this function rather than the deprecated NonEmptyCrossjoin (MDX) function.

Important note Important

Non-empty is a characteristic of the cells references by the tuples, not the tuples themselves.

Examples

The following query shows a simple example of NonEmpty, returning all the Customers who had a non-null value for Internet Sales Amount on July 1st 2001:

SELECT [Measures].[Internet Sales Amount] ON 0,

NONEMPTY(

[Customer].[Customer].[Customer].MEMBERS

, {([Date].[Calendar].[Date].&[20010701], [Measures].[Internet Sales Amount])}

)

ON 1

FROM [Adventure Works]

The following example returns the set of tuples containing customers and purchase dates, using the Filter function and the NonEmpty functions to find the last date that each customer made a purchase:

WITH SET MYROWS AS FILTER

(NONEMPTY

([Customer].[Customer Geography].[Customer].MEMBERS

* [Date].[Date].[Date].MEMBERS

, [Measures].[Internet Sales Amount]

) AS MYSET

, NOT(MYSET.CURRENT.ITEM(0)

IS MYSET.ITEM(RANK(MYSET.CURRENT, MYSET)).ITEM(0))

)

SELECT [Measures].[Internet Sales Amount] ON 0,

MYROWS ON 1

FROM [Adventure Works]

Community Additions

ADD
Show:
© 2015 Microsoft