Data Warehousing and Business Intelligence

9 March 2014

Arguments of the nonempty function

Filed under: Analysis Services — Vincent Rainardi @ 9:02 am

The nonempty function has two arguments: nonempty(a,b).
b is used to evaluate the emptiness.
b can be more than 1. (Note 1)
If b is not supplied SSAS will use the first member. (Note 2)

Example:

nonempty

a) nonempty(country, Risk) evaluates Risk; it returns France and Italy.
b) nonempty(country, Return) evaluates Return; it returns Spain and Italy.
c) nonempty(country, {Risk, Return}) evaluates both and returns all 3.
d) nonempty(country) evaluates Risk, like a). [Note 3]

Notes:
1. To be correct: “b can be a set with more than 1 member”.
2. To be correct: “If b is not supplied it will evaluate the current coordinates of the hierarchies and measures”
3. That is if we have {Risk, Return} on the column axis, i.e. the query is “select {Risk, Return} on columns, …”

Reference:
SSAS MDX Function Reference: link

Working MDX:
(on Adventure Works 2012 cube)

with member x as ([Reseller Sales Amount], [Date].Calendar.PrevMember)
member y as [Reseller Sales Amount]
select {y, x} on columns,
nonempty(Reseller.Reseller.Reseller, {y, x}) on rows
from [Adventure Works]
where [Date].[Calendar].[Calendar Quarter].&[2006]&[4]

Blog at WordPress.com.