Is it possible to select 2 measures in different axis in MDX?

Question

I have a question regarding MDX queries :

I have a SUBSCRIPTION cube about magazine subscriptions with two measures : NEW SUBS. and RETURNING SUBS.

I'm trying to make a query that displays both measures for two periods of time, and also the percentage growth in the period.

Example :

DESIRED VIEW - EXAMPLE

But I can't get this view using standard MDX, because I can't put measures in both axis.

The result I've got so far (not user friendly):

Using measure ON COLUMNS:

RESULT - NOT USER FRIENDLY

How can I do that first view using MDX formulas?

Thanks in advance.

FrankPl FrankPl · Accepted Answer · 2013-08-20T07:48:41

Make growth a member of your time hierarchy like this:

WITH MEMBER [Time].[Calendar Date].[Growth] AS
     [Time].[Calendar Date].[Jan-12] / [Time].[Calendar Date].[Jan-13] - 1
     ,format_string="0%"
SELECT { [Time].[Calendar Date].[Jan-12], [Time].[Calendar Date].[Jan-13], [Time].[Calendar Date].[Growth] }
       ON COLUMNS,
       { Measures.NEW, Measures.RETURNING }
       ON ROWS
  FROM [SUBSCRIPTION]

I was guessing some names of your cube, you will have to adapt the MDX to your situation.

Calculated members need not be a member of the measures hierarchy, they can be used on every hierarchy of your cube. And to answer the question in the title of your question: No, one hierarchy can only be used in one axis in an MDX query.

Is it possible to select 2 measures in different axis in MDX?

1 Answers