MDX - Exclude columns with (null) or zero values

Question

I'm working on a cube query in SSMS 2014, and my query is returning all columns, even those that include 0's and (null) values. I understand this is because the Date and Client Name are not empty.

With my MDX query below, how can I remove any column that includes a (null) value, or a count of zero for logins?

WITH 
  MEMBER [2MonthsPriorUniqueUsers] AS Sum({[Date].[Calendar Year Month].CurrentMember.Lag(2)},[Measures].[App Unique Users Loggedin]) 
  MEMBER [1MonthPriorUniqueUsers] AS Sum({[Date].[Calendar Year Month].CurrentMember.Lag(1)},[Measures].[App Unique Users Loggedin]) 
  MEMBER [1MonthPriorTotalAppLogins] AS Sum({[Date].[Calendar Year Month].CurrentMember.Lag(1)},[Measures].[App Logins])
  MEMBER [Measures].[PercentUniqueIncreaseMoM] AS ([1MonthPriorUniqueUsers] / [2MonthsPriorUniqueUsers])-1,FORMAT_STRING = "Percent" 
SELECT 
   NON EMPTY 
     {[Date].[Calendar Year Month].[201705]}
   *  [Project].[Client Name].members ON 0,
  NON EMPTY 
    {
      [Measures].[PercentUniqueIncreaseMoM]
     ,([1MonthPriorUniqueUsers])
     ,[2MonthsPriorUniqueUsers]
     ,[1MonthPriorTotalAppLogins]
    } ON 1
FROM [MyCube]

Thanks in advance!

Danylo Korostil Danylo Korostil · Accepted Answer · 2017-05-23T10:52:42

You want to use the NonEmpty() or the Filter() functions. For example, if you need to exclude empty records based on [Measures].[App Logins]:

WITH 
  MEMBER [2MonthsPriorUniqueUsers] AS Sum({[Date].[Calendar Year Month].CurrentMember.Lag(2)},[Measures].[App Unique Users Loggedin]) 
  MEMBER [1MonthPriorUniqueUsers] AS Sum({[Date].[Calendar Year Month].CurrentMember.Lag(1)},[Measures].[App Unique Users Loggedin]) 
  MEMBER [1MonthPriorTotalAppLogins] AS Sum({[Date].[Calendar Year Month].CurrentMember.Lag(1)},[Measures].[App Logins])
  MEMBER [Measures].[PercentUniqueIncreaseMoM] AS ([1MonthPriorUniqueUsers] / [2MonthsPriorUniqueUsers])-1,FORMAT_STRING = "Percent" 
SELECT 
   NON EMPTY 
     {[Date].[Calendar Year Month].[201705]}
   *  Filter([Project].[Client Name].members,[Measures].[App Logins]>0) ON 0,
  NON EMPTY 
    {
      [Measures].[PercentUniqueIncreaseMoM]
     ,([1MonthPriorUniqueUsers])
     ,[2MonthsPriorUniqueUsers]
     ,[1MonthPriorTotalAppLogins]
    } ON 1
FROM [MyCube]

MDX - Exclude columns with (null) or zero values

1 Answers