October 21, 2014 at 10:05 am
Hi ,
I am trying to create a calculated member in Analysis server cube (2008 r2) to achieve the following.
I want to display a corresponding measure 2 value based up on another measure (measure 1). The aggregation types for both the measures are MAX.
When you aggregate the values for a hierarchy, first it should show the MAX (Value) of measure 1 and the corresponding value for measure 2.
For measure 1 it is easy. I can set the aggregation type as "MAX" . But for the second measure I need to create a calculated member with an MDX expression.
I have tried an expression which is working only for a single hierarchy. But as soon as I select the other hierarchies it shows wrong values.
Example sheet is uploaded.
Any help will be appreciated.
Thanks
Jay
November 13, 2014 at 8:25 am
Can you give hierarchy 1 and hierarchy 2 levels. little more info required.
November 13, 2014 at 8:46 am
Thanks for your interest. Actually I was able to solve this problem by my self. It was just a matter of finding the tuples for the max value coordinates at the lowest level.
Something like ..
For the first hierarchy
Calculated Member1= topcount(descendants([Date Hierarchy], 4, LEAVES), 1,[Measures1]).Item(0).Member_Unique_Name will give me the lowest level ( Quarter Hour) tuple where the Measure1 is maximum.
Calculated Member2= same as above for the other hierarchy.
Third step is to convert both the Calculated Measures to members of the corresponding dimensions using STRTOMEMBER AS Given below. Once you know the lowest level coordinates then it is matter of creating another calculated member to display the value of the coordinates as given below.
(STRTOMEMBER([Measures].[Calculated Member1]),(STRTOMEMBER([Measures].[Calculated Member2]),[Measure2]))
I am not sure it is best approach. But it works just fine.
Regards
Jay
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply