MDX-过滤多个维度 [英] MDX - Filter multiple dimensions

问题描述

我正在尝试形成一个MDX查询，以便它仅返回度量符合特定条件的两个维度的组合.我认为使用FILTER函数会很简单，即

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]

但是，在运行查询之后，很容易看到我有一个错误，因为第一个结果的点百分比为1.5172，显然大于.95.

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]

我得到了一个类似的结果集，包括高于0.95的值.我是否完全错过了过滤器的要点，或者尝试一次过滤二维是否有问题?

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?

推荐答案

我没有您的数据源，但是此MDX可以与AS2000示例多维数据集Foodmart(销售多维数据集)一起使用.

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

这篇关于MDX-过滤多个维度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！