CODEWITHSUNDEEP
mdx
user1186098
user1186098

Reputation: 43

MDX cannot filter out empty rows

I can't get rid of empty rows in the following MDX output query. I am displaying the last 12 months of revenue for a certain dimension with 4 levels in its hierarchy. I'm filtering this output based on an other dimension in the where statement.

I have tried NON EMPTY, NonEmpty(), FILTER and etc, but I cant get it worked.

Please look at this image.

WITH 
  MEMBER [Measures].[Member Full Path] AS 
    [Revenuedim].CurrentMember.UniqueName 
  MEMBER [Measures].[Member Ordinal] AS 
    [Revenuedim].CurrentMember.Ordinal 
SELECT 
  NON EMPTY 
    Hierarchize
    (
      Union
      (
        CrossJoin
        (
          [Measures].[BeaconAmount_USD]
         ,LastPeriods
          (12
           ,[Date.YQM].LastChild.LastChild.LastChild
          )
        )
       ,CrossJoin
        (
          {
            [Measures].[Member Ordinal]
           ,[Measures].[Member Full Path]
          }
         ,[Date.YQM].[2018]
        )
      )
     ,POST
    ) ON COLUMNS
 ,NON EMPTY 
    Order
    (
      ToggleDrillState
      (
        ToggleDrillState
        (
          ToggleDrillState
          (
            ToggleDrillState
            (
              {[Revenuedim].[All CPs]}
             ,{}
            )
           ,{}
          )
         ,{}
        )
       ,{}
      )
     ,[Measures].[BeaconAmount_USD]
     ,DESC
    ) ON ROWS
FROM [Revenue_CP]
WHERE 
  [Revenuedim_SOB].[TCS BPO Chile S.A.];

Upvotes: 1

Views: 696

Answers (2)

user1186098
user1186098

Reputation: 43

Finally found what I needed ...

WITH 
  MEMBER  [Date.YQM].[Last12M] AS  
    Aggregate(  
  Tail([Date.YQM].[Month].Members,12) )

  MEMBER [Measures].[Member Full Path] AS 
    IIF(
     isEmpty ([Revenuedim].CurrentMember * [Date.YQM].[2017] * [Measures].[BeaconAmount_USD]  * [Revenuedim_SOB].[TCS BPO Chile S.A.]) 
      ,NULL
     ,[Revenuedim].CurrentMember.UniqueName 
    )
  MEMBER [Measures].[Member Ordinal] AS 
    IIF(
     isEmpty( [Revenuedim].CurrentMember * [Date.YQM].[2017] * [Measures].[BeaconAmount_USD] * [Revenuedim_SOB].[TCS BPO Chile S.A.])
     ,NULL 
     ,[Revenuedim].CurrentMember.Ordinal
    )

        SELECT 
        NON EMPTY 
        Hierarchize(

        Union(
                Crossjoin( [Measures].[BeaconAmount_USD]  ,     
                 LastPeriods(12,[Date.YQM].lastChild.lastChild.lastChild)  
                )
            ,   
            Crossjoin({[Measures].[Member Ordinal], [Measures].[Member Full Path]},  [Date.YQM].[Last12M] )
        )
        ,POST) ON COLUMNS,

        NON EMPTY
            ORDER(
            TOGGLEDRILLSTATE(
                TOGGLEDRILLSTATE(
                    TOGGLEDRILLSTATE(
                        TOGGLEDRILLSTATE(
                     {[Revenuedim].[All CPs]}, 
                 {[Revenuedim].[All CPs]}
            ) 
            , {}
         ), {}
        ), {}
    )
 ,[Measures].[BeaconAmount_USD],
 DESC)
ON ROWS from [Revenue_CP]
where [Revenuedim_SOB].[TCS BPO Chile S.A.]

Upvotes: 0

whytheq
whytheq

Reputation: 35557

You can try adding a condition into your custom measures:

WITH 
  MEMBER [Measures].[Member Full Path] AS 
    [Revenuedim].CurrentMember.UniqueName 
  MEMBER [Measures].[Member Ordinal] AS 
    [Revenuedim].CurrentMember.Ordinal 
...
...

So IIF based on a tuple e.g. ([Revenuedim].CurrentMember, [Date.YQM].[2018]) would probably do it:

WITH 
  MEMBER [Measures].[Member Full Path] AS 
    IIF(
     ([Revenuedim].CurrentMember, [Date.YQM].[2018])  = 0
     ,NULL
     ,[Revenuedim].CurrentMember.UniqueName 
    )
  MEMBER [Measures].[Member Ordinal] AS 
    IIF(
     ([Revenuedim].CurrentMember, [Date.YQM].[2018])  = 0
     ,NULL
     ,[Revenuedim].CurrentMember.Ordinal 
    )
...
...

Upvotes: 1

Related Questions