MDX - Filter multiple dimensions

Question

I'm trying to form an MDX query such that it returns only the combinations of two dimensions where a measure meets a certain criteria. I thought this would be pretty straight forward using the FILTER function, i.e.

SELECT
    NON EMPTY FILTER({[Program].[ByRegion].[Program] * [Performance Metric].[Metric].CHILDREN }, [Measures].[Point Percentage] < .95) ON ROWS,
    NON EMPTY ( HIERARCHIZE([Calendar Period].[Y-Q-M].[Month of Quarter].&[3]&[1]&[2009]) , [Measures].[Point Percentage] )ON COLUMNS
FROM [QEP Revenue]

However, after running the query, it is pretty easy to see that I have a mistake because the very first result has a Point Percentage of 1.5172 which is obviously more than .95.

If I completely remove the filter:

SELECT
    --NON EMPTY FILTER({[Program].[ByRegion].[Program] * [Performance Metric].[Metric].CHILDREN }, [Measures].[Point Percentage] < .95) ON ROWS,
    NON EMPTY ({[Program].[ByRegion].[Program] * [Performance Metric].[Metric].CHILDREN }) ON ROWS,
    NON EMPTY ( HIERARCHIZE([Calendar Period].[Y-Q-M].[Month of Quarter].&[3]&[1]&[2009]) , [Measures].[Point Percentage] )ON COLUMNS
FROM [QEP Revenue]

I get a similar result set including values above .95. Am I completely missing the point of a filter, or is there an issue with attempting to filter two dimensions at once?

Answer 1

I got it cracked.

The filter was being applied correctly to the Program and Performance Metric dimensions. The issue was that the filter was applied separately from the Calendar Period dimension. So the Point Percentage of 1.5172 that showed up was allowed to show because there was a Point Percentage in another month that fulfilled the filter requirement.

I was able to rewrite the query as such to get the desired results:

SELECT 
  NON EMPTY 
    Filter
    (
      {
          [Program].[ByRegion].[Program]*
          [Performance Metric].[Metric].Children*
          [Calendar Period].[Y-Q-M].[Month of Quarter].&[3]&[1]&[2009]
      }
     ,
      [Measures].[Point Percentage] < 0.95
    ) ON ROWS
 ,NON EMPTY 
    [Measures].[Point Percentage] ON COLUMNS
FROM [QEP Revenue];

Luckily, this query is being used in reporting services, so it is appropriate to move the Calendar Period into the ROWS. However, if I wanted to keep the Calendar Period in the COLUMNS, I wouldn't know how to solve this since the same dimension cannot be used in both axes.

Answer 2

I don't have your datasource, but this MDX works against the AS2000 sample cube, Foodmart (Sales cube).

SELECT 
  NON EMPTY 
    {{[Time].[Quarter].MEMBERS}} ON COLUMNS
 ,NON EMPTY 
    Filter
    (
      CrossJoin
      (
        {[Customers].[State Province].&[CA]}
       ,[Promotions].[All Promotions].Children
      )
     ,
        (
          [Customers].[State Province].&[CA]
         ,[Time].&[1997].&[Q1]
         ,[Measures].[Unit Sales]
        )
      > 300
    ) ON ROWS
FROM [Sales]
WHERE 
  [Measures].[Unit Sales];