How to get rid of empty cells when combining measures and kpi goals in MDX Query

Question

I have this issue with an MDX query where the NON EMPTY clause isn’t working as I expected after adding KPI goals, trends and status to the query. The basic query looks like this

SELECT 
    NON EMPTY({[Measure1], [Measure2], KPIValue('MyKpi')}) 
    ON COLUMNS, 
    NON EMPTY [Dim Country].[Name].[Name].ALLMEMBERS ON ROWS 
FROM [BIA CO]

returning something like this, which is fine:

            Measure1    Measure2        MyKpi
Canada      7977        4487            3231
USA         6           14              6
UK          442         1179            180

Problems comes when I add KPI goal, trend and status:

SELECT 
NON EMPTY({[Measure1], [Measure2], KPIValue('MyKpi'), KPIGoal('MyKpi'), KPIStatus('MyKpi'), KPITrend('MyKpi')}) 
    ON COLUMNS, 
    NON EMPTY [Dim Country].[Name].[Name]ALLMEMBERS ON ROWS 
FROM [BIA CO]

Which returns something like:

             Measure1   Measure2    MyKpi   MyKpi   Goal    MyKpi Status    MyKpi Trend
Mexico      (null)      (null)  (null)  40300   -1              -1
Cuba        (null)      (null)  (null)  40300   -1              -1
Canada      7977        4487    3231    40300   -1               1
Portugal    (null)      (null)  (null)  40300   -1              -1
China       (null)      (null)  (null)  40300   -1              -1
USA         6           14      6       40300   -1               1
UK          442         1179    180     40300   -1               1

How can I get rid of all those rows with nulls except for the goal, status and trend?

Answer 1

Simply add the Non-empty behavior to your KPIs. If you set Measure1 you'll filter out all empty tuples. It'll inherit the non-empty behavior from the Measure1. Also you may return the the following set on rows with similar logic (the output will be the same):

NonEmpty([Dim Country].[Name].[Name].ALLMEMBERS,[Measures].[Measure1]) on rows

Answer 2

I had a similar problem and this is what I did. In my case I had no control over the cube design. I'm not an expert so wait for some experienced feedback before using it.

WITH
    MEMBER [KPIValue(ReservaKPI)] as KPIValue('ReservaKPI')
    MEMBER [KPIGoal(ReservaKPI)] as CASE WHEN ISEMPTY([KPIValue(ReservaKPI)]) THEN NULL ELSE KPIGoal('ReservaKPI') END
    MEMBER [KPIStatus(ReservaKPI)] as CASE WHEN ISEMPTY([KPIValue(ReservaKPI)]) THEN NULL ELSE KPIStatus('ReservaKPI') END
    MEMBER [KPITrend(ReservaKPI)] as CASE WHEN ISEMPTY([KPIValue(ReservaKPI)]) THEN NULL ELSE KPITrend('ReservaKPI') END
SELECT 
    NON EMPTY({[Cant Adultos], [Cant Noches], [KPIValue(ReservaKPI)], [KPIGoal(ReservaKPI)], [KPIStatus(ReservaKPI)], [KPITrend(ReservaKPI)]}) 
    ON COLUMNS, 
    NON EMPTY [Dim Mun].[NOMBRE].[NOMBRE].ALLMEMBERS ON ROWS 
FROM [BIA CO]

Answer 3

I sometimes like to use the HAVING clause like this maybe:

SELECT 
  NON EMPTY 
    {
      [Measure1]
     ,[Measure2]
     ,KPIValue('MyKpi')
     ,KPIGoal('MyKpi')
     ,KPIStatus('MyKpi')
     ,KPITrend('MyKpi')
    } ON COLUMNS
 ,NON EMPTY 
    [Dim Country].[Name].[Name].ALLMEMBERS HAVING 
  (NOT IsEmpty([Measure1])) AND (NOT IsEmpty([Measure2])) ON ROWS
FROM [BIA CO];

But you could move that logic into a WITH clause as suggested by @user1998299 - I'd probably do a custom set:

WITH 
  SET [SmallSet] AS 
    NonEmpty
    (
      NonEmpty
      (
        [Dim Country].[Name].[Name].ALLMEMBERS
       ,[Measure1]
      )
     ,[Measure2]
    ) 
SELECT 
  NON EMPTY 
    {
      [Measure1]
     ,[Measure2]
     ,KPIValue('MyKpi')
     ,KPIGoal('MyKpi')
     ,KPIStatus('MyKpi')
     ,KPITrend('MyKpi')
    } ON COLUMNS
 ,NON EMPTY 
    [SmallSet] ON ROWS
FROM [BIA CO];