选择度量值>0的度量值

Question

我想数一下

上的不同(维度)用户GUID

(度量) M1 >0和(度量值) M2 >0的特定(维度)月份

我的问题是：

 WITH 
  MEMBER [Measures].[M1 Count] AS 
    Count
    (
      NonEmpty
      (
          NonEmpty
          (
            [AAUser].[USER GUID].[USER GUID].MEMBERS
           ,[Measures].[M1]
          )
        * 
          {[Measures].[M1]}
      )
    ) 
  MEMBER [Measures].[T2 Count] AS 
    Count
    (
      NonEmpty
      (
        NonEmpty
        (
          [AAUser].[USER GUID].[USER GUID].MEMBERS
         ,[Measures].[T2]
        )
       ,{[Measures].[T2]}
      )
    ) 
SELECT 
  {
    [Measures].[M1 Count]
   ,[Measures].[T2 Count]
  } ON COLUMNS
 ,[AATime].[Month].[Month].ALLMEMBERS ON ROWS
FROM [MyCube]
WHERE 
  {
      StrToMember("[AATime].&[2013-11-01T00:00:00]")
    : 
      StrToMember("[AATime].&[2014-10-31T00:00:00]")
  };

我得到的结果是：

​

这是错误的，因为我选择了NONEMPTY，它将返回所有内容(即使值为0)。有人能修改我的查询并过滤(where [Measures].[M1] > 0 and [Measures].[M2] > 0)吗？

Answer 1

(未测试)也许您可以使用函数filter

WITH 
  MEMBER [Measures].[M1 Count] AS 
    Count
    (
      NonEmpty
      (
          NonEmpty
          (
            filter(
              [AAUser].[USER GUID].[USER GUID].MEMBERS
              , ([Measures].[M1] > 0 AND [Measures].[M2] > 0)
            )
           ,[Measures].[M1]
          )
        * 
          {[Measures].[M1]}
      )
    ) 
  MEMBER [Measures].[T2 Count] AS 
    Count
    (
      NonEmpty
      (
        NonEmpty
        (
            filter(
              [AAUser].[USER GUID].[USER GUID].MEMBERS
              , ([Measures].[M1] > 0 AND [Measures].[M2] > 0)
            )          
         ,[Measures].[T2]
        )
       ,{[Measures].[T2]}
      )
    ) 
SELECT 
  {
    [Measures].[M1 Count]
   ,[Measures].[T2 Count]
  } ON COLUMNS
 ,[AATime].[Month].[Month].ALLMEMBERS ON ROWS
FROM [MyCube]

但是也许沿着这些路线的东西会更好呢？

WITH 
  MEMBER [Measures].[M1 Count] AS 
    Sum
    (
      [AAUser].[USER GUID].[USER GUID]
     ,IIF
      (
        [Measures].[M1] > 0 AND [Measures].[M2] > 0
       ,1
       ,NULL
      )
    ) 
  MEMBER [Measures].[T2 Count] AS 
    Sum
    (
      NonEmpty
      (
        [AAUser].[USER GUID].[USER GUID]
       ,[Measures].[T2]
      )
     ,IIF
      (
        [Measures].[M1] > 0 AND [Measures].[M2] > 0
       ,1
       ,NULL
      )
    ) 
SELECT 
  {
    [Measures].[M1 Count]
   ,[Measures].[T2 Count]
  } ON COLUMNS
 ,[AATime].[Month].[Month].ALLMEMBERS ON ROWS
FROM [MyCube];