The MDX NON EMPTY Keyword will write the all the specified member whose values are not NULL. For example, When we are displaying the Non performing products, you may find few products with NULLs. In order to remove the NULLs we can use this NON EMPTY Keyword.
Please refer below table to understand the MDX NON EMPTY output.
| Measure 1 Value | Measure 2 Value | NON EMPTY Result |
|---|---|---|
| NULL | NULL | NON Empty keyword will not display this record |
| NULL | NOT NULL | NON Empty will display the record |
| NOT NULL | NULL | NON Empty will display the record |
| NOT NULL | NOT NULL | NON Empty will display the record |
In this article we will show you, How to use NON EMPTY Keyword in Multidimensional Expression or MDX query with examples. For this, We are going to use the below show data
MDX NON Empty Example
In this example we are going to use Non Empty to find the Reseller Sales Amount of every Calender Year whose Sale value is Not Null
MDX CODE
SELECT [Measures].[Reseller Sales Amount] ON COLUMNS, NON EMPTY [Date].[Calendar].[Calendar Year].ALLMEMBERS ON ROWS FROM [Adventure Works]
OUTPUT
Analysis:
In the above MDX Query, We used [Reseller Sales amount] on Columns
[Measures].[Reseller Sales Amount] ON COLUMNS
Non Empty keyword in the below lines of code will find the Calender Year whose Reseller Sales Amount is Not NULL and displays them as output.
NON EMPTY [Date].[Calendar].[Calendar Year].ALLMEMBERS ON ROWS
As we all know, there is no sales from Calender Year 2005 to 2009 and also 2014 that’s why Non Empty is displaying Reseller Sales for Calender Year 2010, 2011, 2012 and 2013
MDX Non Empty Example 2
In this example we are going to use multiple Measures to show you the Non Empty behavior more closely
MDX CODE
SELECT
{ [Measures].[Reseller Sales Amount],
[Measures].[Internet Sales Amount]
} ON COLUMNS,
NON EMPTY [Date].[Calendar].[Calendar Year].ALLMEMBERS ON ROWS
FROM [Adventure Works]
OUTPUT
ANALYSIS
In the above MDX Query, We used [Reseller Sales amount] and [Internet Sales amount] on Columns
{
[Measures].[Reseller Sales Amount],
[Measures].[Internet Sales Amount]
} ON COLUMNS
Non Empty keyword in the below lines of code will find the Calender Years whose [Reseller Sales amount] and [Internet Sales amount] values are Not NULL. It will display the result as we explained in the above table.
NON EMPTY [Date].[Calendar].[Calendar Year].ALLMEMBERS ON ROWS
As we all know, there is no Reseller sales and Internet Sale from Calender Year 2005 to 2009 that’s why Non Empty is displaying Reseller Sales for Calendar Year 2010, 2011, 2012, 2013 and 2014.
Thank You for Visiting Our Blog
