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

AlwaysLearning picture AlwaysLearning · Dec 16, 2013 · Viewed 16.8k times · Source

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

FrankPl picture FrankPl · Dec 16, 2013

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]