MDX using IIF and SUM

问题:

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

回答1:

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.

回答2:

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