MDX using IIF and SUM

2019-09-07 09:00发布

At the following MDX code I want to get the aggregate of measure only for members that are in the specified last time (like in example 01/06/2015), else I don't want them .
I tried existing and exists but without any luck.
I believe i have to use the IIF function like:

IIF DAYTIME, MEASURE D NOT NULL THEN AGGREGATE....

(if for the specific month, measure D is bigger than 0 then SUM measure D for the specific time range)

ELSE NULL.

(Else not print it on output)

And then filter E.members where SUM measure D >1 ON ROWS.

WITH 
  MEMBER A AS 
    b + C 
  MEMBER [Measures].[Aggregate] AS 
    Aggregate
    (
        DAYTIME.[Month].&[2013-01-01T00:00:00]
      : 
        [DAYTIME].[Month].&[2015-06-01T00:00:00]
     ,[Measures].[D]
    ) 
SELECT 
  [Measures].[Aggregate] ON 0
 ,Filter
  (
    Exists
    (
      [E].MEMBERS
     ,[DAYTIME].[Month].&[2015-06-01T00:00:00]
    )
   ,
    [Measures].[Aggregate] > 1
  ) ON 1
FROM [CUBE];

2条回答
狗以群分
2楼-- · 2019-09-07 09:42

Try using Sum rather than Aggregate. Also as [DAYTIME].[Month].&[2015-06-01T00:00:00] is part of the measure [Measures].[Aggregate] you could delete the Exists function:

WITH 
  MEMBER [Measures].[Aggregate] AS 
    Sum
    (
        DAYTIME.[Month].&[2013-01-01T00:00:00]
      : 
        [DAYTIME].[Month].&[2015-06-01T00:00:00]
     ,[Measures].[D]
    ) 
SELECT 
  [Measures].[Aggregate] ON 0
 ,Filter
  (
    [E].MEMBERS
   ,[Measures].[Aggregate] > 1
  ) ON 1
FROM [CUBE];

Note: it is important that [Measures].[D] is related to these members [E].MEMBERS

查看更多
我想做一个坏孩纸
3楼-- · 2019-09-07 09:50

Someone from MSDN forums reminded me the use of NONEMPTY. So first I used NONEMPTY like Nonempty ( [E] , ( [DAYTIME].[Month].&[2015-06-01T00:00:00], [measures].[D] ) ) in order to return all E.Members who had an non-null value for measure.D on 2015-06-01 Then I filter this with another measure

I don't get correct data but I believe there is problem inside BI solution.

查看更多
登录 后发表回答