Ordering by a generic calculated measure results i

Question

I need to write a calculated measure that would show a share of Measure1 comparing to the total sum by any given dimension. I managed to do it like this:

CREATE MEMBER CURRENTCUBE.[Measures].[Test] AS
  [Measures].[Measure1] / ( AXIS( 1 ).ITEM( 0 ).DIMENSION.LEVELS( 0 ).ITEM( 0 ), [Measures].[Measure1] )

But due to the generic nature of [Test], it is not possible to order a dimension by this measure.

SELECT [Measures].[Measure1] ON 0,
ORDER( [Dimension1].MEMBERS, [Measures].[Test] ) ON 1
FROM [MyCube]

Executing the above code results in the following error:

Infinite recursion detected. The loop of dependencies is: Test -> Test.

Which is rather logical — to get AXIS( 1 ).ITEM( 0 ), we need a set of dimension members on axis 1, but this set is impossible to obtain until the members are sorted by [Test].

On the other hand, if I define [Test] as specific to some dimension, it works as expected:

CREATE MEMBER CURRENTCUBE.[Measures].[Test] AS
  [Measures].[Measure1] / ( [Dimension1].[All], [Measures].[Measure1] )

It works, because [Dimension1].[All] addresses a particular member, without requiring axis 1 to evaluate its member set first.

Is there a way to make this calculated measure generic? Maybe, it's possible to get current dimension's [All] member in another way?

Answer 1

If you are only displaying one measure, you could rewrite your first query to avoid the error:

SELECT [Measures].[Measure1] ON 0,
ORDER([Dimension1].MEMBERS) ON 1
FROM [MyCube]
WHERE ([Measures].[Test])