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.
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.];