Tutorial Gateway

  • C
  • C#
  • Java
  • Python
  • SQL
  • MySQL
  • Js
  • BI Tools
    • Informatica
    • Talend
    • Tableau
    • Power BI
    • SSIS
    • SSRS
    • SSAS
    • MDX
    • R Tutorial
    • Alteryx
    • QlikView
  • More
    • C Programs
    • C++ Programs
    • Python Programs
    • Java Programs

MDX NON EMPTY

by suresh

The MDX NON EMPTY Keyword will write all the specified member whose values are not NULL. For example, When we are displaying Non-performing products, you may find a few products with NULLs. To remove the NULLs, we can use this MDX NON EMPTY Keyword.

Please refer below table to understand the MDX NON EMPTY output.

Measure 1 ValueMeasure 2 ValueNON EMPTY Result
NULLNULLNON Empty keyword will not display this record
NULLNOT NULLNON Empty will display the record
NOT NULLNULLNON Empty will display the record
NOT NULLNOT NULLNON 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 1

MDX NON Empty Example

In this example, we are going to use the Non Empty to find the Reseller Sales Amount of every Calendar Year whose Sale value is Not Null

SELECT 
  [Measures].[Reseller Sales Amount] ON COLUMNS,
  NON EMPTY [Date].[Calendar].[Calendar Year].ALLMEMBERS ON ROWS
FROM [Adventure Works]

OUTPUT

MDX NON EMPTY 2
Analysis:

In the above MDX Query, We used [Reseller Sales amount] on Columns

[Measures].[Reseller Sales Amount] ON COLUMNS

The MDX 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 are no sales from Calendar Year 2005 to 2009 and also 2014 that’s why Non Empty is displaying Reseller Sales for Calendar Year 2010, 2011, 2012 and 2013

Non Empty Example 2

In this example, we are going to use multiple Measures to show you the Non Empty behavior more closely

SELECT 
  { [Measures].[Reseller Sales Amount],
    [Measures].[Internet Sales Amount]
  } ON COLUMNS,
  NON EMPTY [Date].[Calendar].[Calendar Year].ALLMEMBERS ON ROWS
FROM [Adventure Works]

OUTPUT

MDX NON EMPTY 3

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

The MDX 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 Calendar Year 2005 to 2009. That’s why Non Empty is displaying Reseller Sales for Calendar Year 2010, 2011, 2012, 2013, and 2014.

Placed Under: MDX

  • MDX Ascendants Function
  • MDX AVG Function
  • MDX BottomCount Function
  • MDX BottomPercent Function
  • MDX BottomSum Function
  • MDX Children Function
  • MDX ClosingPeriod Function
  • MDX COUSIN FUNCTION
  • MDX FirstChild Function
  • MDX FirstSibling Function
  • MDX Head Function
  • MDX LastChild Function
  • MDX LastSibling Function
  • MDX LastPeriods Function
  • MDX LAG Function
  • MDX LEAD Function
  • MDX NextMember Function
  • MDX NONEMPTY Function
  • MDX NON EMPTY
  • MDX OpeningPeriod Function
  • MDX ParallelPeriod Function
  • MDX PeriodsToDate Function
  • MDX PrevMember Function
  • MDX Parent Function
  • MDX Siblings Function
  • MDX Tail Function
  • MDX TOPSUM Function
  • MDX TopPercent Function
  • MDX TopCount Function
  • C Tutorial
  • C# Tutorial
  • Java Tutorial
  • JavaScript Tutorial
  • Python Tutorial
  • MySQL Tutorial
  • SQL Server Tutorial
  • R Tutorial
  • Power BI Tutorial
  • Tableau Tutorial
  • SSIS Tutorial
  • SSRS Tutorial
  • Informatica Tutorial
  • Talend Tutorial
  • C Programs
  • C++ Programs
  • Java Programs
  • Python Programs
  • MDX Tutorial
  • SSAS Tutorial
  • QlikView Tutorial

Copyright © 2021 | Tutorial Gateway· All Rights Reserved by Suresh

Home | About Us | Contact Us | Privacy Policy