MDX: Filtering and slicing on different members of the same dimension

Question

I am using icCube's Sales cube to learn MDX. I would like to write an MDX query to show the revenue from sales in 2009 for those countries where the revenue from sales in 2010 was over $80,000. I tried the following, but it returned an empty table with only one column ([Amount]):

WITH
 SET [myset] AS Filter([Country].members, ([Measures].[Amount], [2010])>80000)
select [Country].members on 0, [Amount] on 1
from (select [myset] on 0, [Amount] on 1 from [sales])
where [2009]

Answer 1

UPDATE : The tuple evaluation is a case we missed, the bug has been fixed in icCube 4.2. Pay attention that using the same hierarchy on a where clause and an axis it's tricky. Any sets is filtered out : "{[2010],[2011]} on 0 .. where [2010] will return [2010] but a tuple is evaluted without filtering. Different hierarchies of the same dimension will apply the autoexists filter.

As this is a bit tricky we decided to extend SETS to allow to declare a set in a query that is evaluated with the global context - without taking into account where clause and subqueries :

WITH
-- always the same regardless of the where clause and subquery
  STATIC SET [Global] Filter([Country].members, ([Measures].[Amount], [2010])>80000)
..

---

The easy and most efficient solution is to go as Frank is proposing, adding directly the filtered countries in the axis.

SELECT 
   // this will return the countries with sales amount bigger than 80'000 for [2010]
   // e.g. { [USA], [UK] } ... [2010] is is just used for filterting
   Filter([Country].members, ([Measures].[Amount], [2010])>80000) on 0,
   // When evaluated we're going to use [2009] as it's the slicer default value
   [Amount] on 1
FROM [sales]
WHERE [2009]

Regarding your query it should work, I agree, it's a bug in icCube that we will fix asap (PTS). In the meantime you have to avoid using the same hierarchy in the slicer and in the Filter function used in a slicer, something like :

WITH 
 MEMBER [Amount 2009] AS ([Amount],[2009])
SELECT 
  Filter([Country].members, ([Measures].[Amount], [2010])>80000) on 0,
  [Amount 2009] on 1
FROM [sales]

Or you can change to a version that works in icCube (less efficient than Frank's one) :

SELECT [Country].members on 0, [Amount] on 1
FROM (select  Filter([Country].members, ([Measures].[Amount], [2010])>80000) on 0 from [sales])
WHERE [2009]

If you're playing with grouping based on different critierias you can use a new feature icCube introduced a couple of months ago : Categories

They allow to create a hierarchy as member of other hierarchies. You could define those countries as [80000+] to afterwards perform calculations based on this new clasification.

Answer 2

Just use

WITH
 SET [myset] AS Filter([Country].members, ([Measures].[Amount], [2010])>80000)
select [myset] on 0, [Amount] on 1
from [sales]
where [2009]

In MDX, this type of query does not need a subselect or WHERE. And you even could omit the set myset, writing

select Filter([Country].members, ([Measures].[Amount], [2010])>80000) on 0,
       [Amount] on 1
from [sales]
where [2009]

I tried a similar query on Adventure works, and it shows All Customers, Australia, Canada, and US, but not France, Germany, and UK:

SELECT Filter([Customer].[Country].Members,
              ([Measures].[Internet Sales Amount], [Date].[Calendar Year].&[2006]) > 600000
             )
       ON 0,
       {[Measures].[Internet Sales Amount]}
       ON 1
FROM [Adventure Works]
WHERE [Date].[Calendar Year].&[2008]