CODEWITHSUNDEEP
ssasmdxcube
Peter
Peter

Reputation: 133

MDX WHERE vs FILTER options

I have a query in which I need to do some filtering. I can do it in a subcube, but I am wondering if I could do this in a WHERE clause without subcube. I think this solution would be faster/cleaner. I need to filter out product models with IB>0 in last month, this is my solution so far (only part of a query):

SELECT  {[Measures].[AFR],[Measures].[IB]} ON COLUMNS,
([dim_ProductModel].[ODM].children)*[Dim_Date].[Date Full].children  ON ROWS 
FROM 
(
    SELECT 
FILTER([dim_ProductModel].[Product Model].children,
([Measures].[IB]*[Dim_Date].[Date Full].&[2014-04-01]>0)) ON COLUMNS FROM
[cub_dashboard_spares]
)

however, I would prefer to have it in one query without subquery something like this (its not working though):

SELECT  {[Measures].[AFR],[Measures].[IB]} ON COLUMNS,
([dim_ProductModel].[ODM].children)*[Dim_Date].[Date Full].children  ON ROWS 
FROM 
[cub_dashboard_spares]
WHERE FILTER([dim_ProductModel].[Product Model].children,
([Measures].[IB]*[Dim_Date].[Date Full].&[2014-04-01]>0))

I get some error message kind of: he MDX function CURRENTMEMBER failed because the coordinate for the ... contains a set..

I basically understand why is he not accepting is as in an WHERE clause I should be more specific but I wonder if there is some possibility to rewrite it so that it works.

I don't want that ProductModel appears in the results set.

Upvotes: 3

Views: 1137

Answers (1)

whytheq
whytheq

Reputation: 35557

SELECT  {[Measures].[AFR],[Measures].[IB]} ON COLUMNS,
([dim_ProductModel].[ODM].children)*[Dim_Date].[Date Full].children  ON ROWS 
FROM 
[cub_dashboard_spares]
WHERE 
   ({[dim_ProductModel].[Product Model].children},
    [Measures].[IB],
    PERIODSTODATE( 
      [Dim_Date].[Date Full], //<<needs to be a level from your Dim_date
      [Dim_Date].[Date Full].&[2014-04-01])  //<<needs to be a member from the levelyou have used in above argument
      )

Upvotes: 1

Related Questions