Search code examples
ssasmdxcross-joinssas-2012measures

MDX - Why Cross join between measures do not work?


In MDX, we can CROSS JOIN two members, a measure and a member but not two measures. Why is this so? What does it imply?

SELECT
   [Measures].[xyz] * [DimTable1].[SomeHierarchy].[Level] on 0,
   [DimTable2].[SomeOtherHierarchy].&[Value] on 1 
FROM [MyCube] 
// WORKS

SELECT 
   [Measures].[xyz]  on 0,
   [DimTable2].[SomeOtherHierarchy].&[Value] * [DimTable1].[SomeHierarchy].[Level] on 1 
FROM [MyCube] 
// OF COURSE IT WORKS

SELECT
    [Measures].[xyz] * [Measures].[ABC] on 0,
    [DimTable1].[SomeHierarchy].&[Value] on 1 
FROM [MyCube] 
// DOES NOT WORK!!

Solution

  • I believe you forgot:

    SELECT [dd].[hh].[mm1] * [dd].[hh].[mm2] on 0, [DimTable1].[SomeHierarchy].&[Value] on 1 FROM [MyCube]

    did not work neither. [Measures] is not different than [dd] in my example. In MDX you cannot define a tuple with _ several members _ of the _ same hierarchy _. Have a look to this gentle introduction explaining the main concepts.