MDX Calculated member in WHERE clause causes different results

Question

The following query has different result sets depending on which of the [Measures] I use in the WHERE clause. Having the [Measures].[MetricFormula] instead of [Measures].[Actual] activated, the query delivers a different (and expected) result.

WITH
MEMBER [Measures].[MetricFormula] AS Measures.[Downloads]
MEMBER [Measures].[Actual] AS [Measures].[MetricFormula], LANGUAGE = 1031,FORMAT_STRING='#,##0'

SET [mySet] as 
topcount(
    nonempty(
        filter(
            distinct descendants({[AppPackages].[AppPackages].&[F_TAAM00000069]}), 
            [Measures].[MetricFormula] <> 0 AND ([AppPackages].[AppPackages].PROPERTIES('AppTypeID') = 'DETAIL')
            ),
        [Time].[Calendar].[Date].&[2015-03-22T00:00:00]:[Time].[Calendar].[Date].&[2015-03-31T00:00:00]
        )
    ,6
    ,aggregate([Time].[Calendar].[Date].&[2015-03-22T00:00:00]:[Time].[Calendar].[Date].&[2015-03-31T00:00:00], [Measures].[MetricFormula]))


select
([Time].[Calendar].[Date].&[2015-03-22T00:00:00]:[Time].[Calendar].[Date].&[2015-03-31T00:00:00]) ON COLUMNS,
([mySet]) ON ROWS   

FROM [Facts] 
WHERE (
--([Measures].[MetricFormula]),
([Measures].[Actual]),
{[DownloadTypes].[DownloadTypes].&[New]}
)

([Measures].[Downloads] is the real measure defined in the cube.)

Question: Why does it deliver different results? I use this construct (in various forms) to simplify code based query generation. I'd like to understand the differnces before I start reworking my query generator.

Background: The query is supposed to calculate the Top 6 descendants of a given AppPackage for a given time range, based on the given measure. I start by finding the (non empty) descendants for the time range, on which I add the topcount, making sure that the time aggregated measure value drives the top calculation. Finally I put the evaluated set on the rows, the time on columns, specifying by where what to see. It works out nicely, besides the described effect.

Answer 1

I had fun replicating in AdvWrks but here it is:

WITH 
  MEMBER [Measures].[x] AS 
    [Measures].[Internet Order Count] 
  MEMBER [Measures].[y] AS 
    [Measures].[x] 
   ,LANGUAGE = 1031 
   ,FORMAT_STRING = '#,##0' 
  SET [s] AS 
    TopCount
    (
      NonEmpty
      (
        Filter
        (
          [State-Province].[State-Province].MEMBERS
         ,
          [Measures].[x] <> 0
        )
       ,
          [Date].[Calendar].[Month].&[2005]&[10]
        : 
          [Date].[Calendar].[Month].&[2006]&[5]
      )
     ,10
     ,Aggregate
      (
          [Date].[Calendar].[Month].&[2005]&[10]
        : 
          [Date].[Calendar].[Month].&[2006]&[5]
       ,[Measures].[x]
      )
    ) 
SELECT 
  {
      [Date].[Calendar].[Month].&[2005]&[10]
    : 
      [Date].[Calendar].[Month].&[2006]&[5]
  } ON COLUMNS
 ,Order
  (
    [s]
   ,[State-Province].CurrentMember.Member_Caption
   ,basc
  ) ON ROWS
FROM [Adventure Works]
WHERE 
  (
    //([Measures].[x]),
    ([Measures].[y]),
   {[Product].[Product Categories].[Category].&[1]}
  );

It does the same as your script - if we change to [x] then South Australia disappears from the result set.

---

Edit
As to why - I now know!

MDX is being very kind to you and not erroring even though there is an error in the script:

Aggregate
      (
          [Date].[Calendar].[Month].&[2005]&[10]
        : 
          [Date].[Calendar].[Month].&[2006]&[5]
       ,[Measures].[x]  //<<this is not allowed so `mdx` is ignoring it!
      )

So try the following OR try swapping Aggregate to Sum:

WITH 
  MEMBER [Measures].[MetricFormula] AS 
    Measures.[Downloads] 
  MEMBER [Measures].[Actual] AS 
    [Measures].[MetricFormula] 
   ,LANGUAGE = 1031 
   ,FORMAT_STRING = '#,##0' 
  SET [mySet] AS 
    TopCount
    (
      NonEmpty
      (
        Filter
        (
          (DISTINCT 
            Descendants({[AppPackages].[AppPackages].&[F_TAAM00000069]}))
         ,
            [Measures].[MetricFormula] <> 0
          AND 
            [AppPackages].[AppPackages].Properties('AppTypeID') = 'DETAIL'
        )
       ,
          [Time].[Calendar].[Date].&[2015-03-22T00:00:00]
        : 
          [Time].[Calendar].[Date].&[2015-03-31T00:00:00]
      )
     ,6
     ,Aggregate
      (
          [Time].[Calendar].[Date].&[2015-03-22T00:00:00]
        : 
          [Time].[Calendar].[Date].&[2015-03-31T00:00:00]
       ,Measures.[Downloads]   //<<<<<<<<<<<<moved away from using a calculated measure as they are not working as expected inside the function `AGGREGATE`
      )
    ) 
SELECT 
    [Time].[Calendar].[Date].&[2015-03-22T00:00:00]
  : 
    [Time].[Calendar].[Date].&[2015-03-31T00:00:00] ON COLUMNS
 ,[mySet] ON ROWS
FROM [Facts]
WHERE 
  (
    --([Measures].[MetricFormula]),
    [Measures].[Actual]
   ,{[DownloadTypes].[DownloadTypes].&[New]}
  );

WHY?

Well using the custom measure [Measures].[MetricFormula] inside the Aggregate function seems to be illegal, but when you do this as part of the second argument in TopCount mdx just ignores the error and uses whatever the default measure is in your cube .... very kind of the engine to ignore that error?!

---

EXAMPLE OF THE ERROR THROWN WHEN FEEDING A CALCULATED MEMBER INTO THE AGGREGATE FUNCTION:

WITH 
  MEMBER [Measures].[j] AS 
    [Measures].[Internet Order Count] 
  MEMBER [Measures].[x] AS 
    Aggregate
    (
        [Date].[Calendar].[Month].&[2005]&[10]
      : 
        [Date].[Calendar].[Month].&[2006]&[5]
     ,[Measures].[j]   //<<FEEDING "ILLEGAL" CALCULATED MEMBER INTO AGGREGATE FUNCTION
    ) 
SELECT 
  {[Measures].[x]} ON COLUMNS
 ,[State-Province].[State-Province].MEMBERS ON ROWS
FROM [Adventure Works];