multiple dimension restrictions in a MDX where clause

I have the following problem. If I query values with a keyfigure which is a function I can't specify multiple values of the same dimension restriction, but if it is not a function it works.

So this works:

SELECT {[Measures].[Netto]} on columns FROM TDC where
({NonEmpty([Time].[Month].[Month].&[2008-03-01T00:00:00]), 
  NonEmpty([Time].[Month].[Month].&[2008-04-01T00:00:00])})

But this doesn't:

SELECT {[Measures].[CalculatedFunction]} on columns FROM TDC where
({NonEmpty([Time].[Month].[Month].&[2008-03-01T00:00:00]), 
 NonEmpty([Time].[Month].[Month].&[2008-04-01T00:00:00])})

And this also works:

SELECT {[Measures].[CalculatedFunction]} on columns FROM TDC where 
({NonEmpty([Time].[Month].[Month].&[2008-03-01T00:00:00])})

I guess the solution is something like adding the where clause to the header but I really like this solution because it's so simple.

The Calucated function is:

CREATE MEMBER CURRENTCUBE.[MEASURES].Ultimo
AS (iif ((not [Time].[Year - Month - Date].currentmember is [Time].[Year - Month - Date].defaultmember),
IIF(NOT ([Measures].[LagerStk] = 0),
Sum([Time].[Year - Month - Date].[Date].members(0):
ClosingPeriod([Time].[Year - Month - Date].[Date]),
[Measures].[LagerStk]), NULL)
, 
IIF(NOT ([Measures].[LagerStk] = 0),
Sum([Time].[Year - Week - Date].[Date].members(0):
ClosingPeriod([Time].[Year - Week - Date].[Date]),
[Measures].[LagerStk]), NULL))), 
VISIBLE = 1;

The code is inspired from this and modified for two hierarchies in the time dimension: http://www.sqlserveranalysisservices.com/OLAPPapers/InventoryManagement%20in%20AS2005v2.htm

This is on SQL server 2005 Enterprise edition.

From stackoverflow

• Ok, this works:

  WITH MEMBER [Time].[Month].a AGGREGATE 
  ({[Time].[Month].[Month].&[2008-03-01T00:00:00], 
    [Time].[Month].[Month].&[2008-04-01T00:00:00]})
  SELECT {[Measures].[CalculatedFunction]} on columns FROM TDC where a
  

• The problem is in your calculated measure. You are using .CurrentMember and ClosingPeriod without a specific member reference which implies a call to .CurrentMember. When you have set in the WHERE clause there is no "Current" member - there are multiple current members. Re-writting your MDX to something like the following should allow it to work with multiple members in the WHERE clause.

  CREATE 
    MEMBER CURRENTCUBE.[MEASURES].Ultimo AS NULL;
  
  SCOPE ([MEASURES].Ultimo);
        SCOPE ([Time].[Year - Month - Date].[All]);
          this = IIF
         (
        (NOT 
          [Measures].[LagerStk] = 0)
          ,Sum
        (
         NULL:Tail(Existing [Time].[Year - Week - Date].[Date],1).item(0).item(0)
         ,[Measures].[LagerStk]
        )
          ,NULL
         );
        END SCOPE;
  
        SCOPE ([Time].[Year - Week - Date].[All]);
       this = IIF
         (
        (NOT 
          [Measures].[LagerStk] = 0)
          ,Sum
        (
         NULL:Tail(Existing [Time].[Year - Month - Date].[Date],1).Item(0).Item(0)
         ,[Measures].[LagerStk]
        )
          ,NULL
         )
       ); 
       END SCOPE;
  END SCOPE;
  

  I am using SCOPE on the All members of the two dimensions, this should be fast that the out IIF and will also avoid one reference to .CurrentMember. I then replaced the ClosingPeriod() call with Tail(Existing [Time].[Year - Week - Date].[Date],1).item(0).item(0) what this does is to get the set of date members that exist in the current context, the Tail() call then gets the last on of these as a single member set and the .Item(0).Item(0) calls get the first member from the first tuple of that set.

  Obviously, not having access to your cube, I can't test any of this. The issue you reported in your comment could relate to either an incorrect reference to the All members (I may have a different naming format to the one in your cube) or it may be related to the IIF() statement. I'm not sure that the check for 0 is being evaluated in the correct context

  You could try testing without the IIF()

  CREATE MEMBER CURRENTCUBE.[MEASURES].Ultimo AS NULL;

  SCOPE ([MEASURES].Ultimo); SCOPE ([Time].[Year - Month - Date].[All]); this = Sum ( NULL:Tail(Existing [Time].[Year - Week - Date].[Date],1).item(0).item(0) ,[Measures].[LagerStk] ); END SCOPE;

    SCOPE ([Time].[Year - Week - Date].[All]);
   this = Sum
    (
     NULL:Tail(Existing [Time].[Year - Month - Date].[Date],1).Item(0).Item(0)
     ,[Measures].[LagerStk]
    ); 
   END SCOPE;
  

  END SCOPE;

  Anders Rune Jensen : Added calucated function to original description. Thanks!

  Anders Rune Jensen : Thanks that works, small nitpick there is a bug in the second scope. Should be: ');', instead of '),)'.

  Anders Rune Jensen : Well it doesn't work actually. If I put either week, date or month on one the axes it doesn't calculate anything?