MDX NONEMPTY Function

The MDX NONEMPTY Function used to display records whose values are not Null. For example, When we are displaying the least Sales by Country-wise or Year wise, you may find NULLs as well. It won’t be very pleasant to see NULL values in High-level data. To remove the NULLs, we can use this MDX NONEMPTY function with the second argument as Sales.

MDX NonEmpty Function Syntax

The basic syntax of the MDX NonEmpty is:

NONEMPTY( Set_Expression, Measure )
  • Set_Expression: Any Multidimensional Expression that returns valid Set.
  • Measure: Any Multidimensional member which returns numeric value. NONEMPTY Function will check against this value to find the Non-Empty or Not NULL records.

For this MDX NONEMPTY Function example, We are going to use the below show data

MDX NONEMPTY Function

MDX NonEmpty Function Example

In this example, we are going to use NonEmpty function to find the Calendar Year whose Internet Sale Amount is Not Null

SELECT 
  { [Measures].[Reseller Sales Amount],
    [Measures].[Internet Sales Amount]
  } ON COLUMNS,
  NONEMPTY (
         [Date].[Calendar].[Calendar Year].ALLMEMBERS,
	 [Measures].[Internet Sales Amount]
	   )ON ROWS
FROM [Adventure Works]
MDX NONEMPTY FUNCTION

In the below MDX Query, We used [Reseller Sales amount] and [Internet Sales amount] on Columns

{
 [Measures].[Reseller Sales Amount],
 [Measures].[Internet Sales Amount]
} ON COLUMNS

The first line of code after the NONEMPTY (first argument) will return all the members present in [Date].[Calendar].[Calendar Year]. It means the first line of code will bring all the calendar years in Date Dimension (2005 to 2014).

[Date].[Calendar].[Calendar Year].ALLMEMBERS

Next, We assigned the [Internet Sales Amount] as the second argument for the MDX NonEmpty function. It means, Below line of code will check the Internet Sales Amount of each Calendar Year returned by the First argument.

  • If the Calendar Year has no Internet Sales, then the NonEmpty function will not return the corresponding row. If you observe the first image, there is no Internet sales from Calendar Year 2005 to 2009
  • If the Calendar Year has Internet Sales, then the NonEmpty function will return the corresponding row.
NONEMPTY (
         [Date].[Calendar].[Calendar Year].ALLMEMBERS,
	 [Measures].[Internet Sales Amount]
	   )ON ROWS

MDX NonEmpty Function Example 2

In this example, we are going to use the above example, but we are going to change the second argument of a NonEmpty function. Please be careful while assigning the second argument to the MDX NonEmpty function. If you place the wrong measure as the second argument, you will end up with the wrong results

SELECT 
  { [Measures].[Reseller Sales Amount],
    [Measures].[Internet Sales Amount]
  } ON COLUMNS,
  NONEMPTY (
         [Date].[Calendar].[Calendar Year].ALLMEMBERS,
	 [Measures].[Reseller Sales Amount]
	   )ON ROWS
FROM [Adventure Works]
MDX NONEMPTY FUNCTION

If you observe the below MDX Nonempty Query, We assigned the [Reseller Sales Amount] as the second argument for NonEmpty function. It means the Below line of code will check the Reseller Sales Amount of each Calendar Year returned by the First argument.

  • If the Calendar Year has no Reseller Sales, then the NonEmpty function will not return the corresponding row. If you observe the first image, there are no Reseller sales from Calendar Year 2005 to 2009 and 2014
  • If the Calendar Year has Reseller Sales, then the NonEmpty function will return the corresponding row.
NONEMPTY (
         [Date].[Calendar].[Calendar Year].ALLMEMBERS,
	 [Measures].[Reseller Sales Amount]
	   )ON ROWS
Categories MDX