COMPARE multiple measure across a Hierachy/Dimension using MDX

Question

HELLO,

We are trying to mask the second highest value from any of the measures across every year * every district.

Example in the above table, lets take District = 030901 and we have the various measure values  (Disregard the COUNTER measure)  as

9 0 8 15 1 0

we need to mask 9 as . and the flags should be

. 0 8 15 1 0

 

The problem is traversing across multiple measures using MDX .

The  documentation  talks about traversing down the hierarchies and dimensions and using a single measure .

Can anyone point me to a documentation /Example on how to traverse/get values for individual measures across the same dimension, i.e, traverse horizontally and not Top-Down ?

NOTE: The tool that I am trying to do this is not SQL SERVER REPORTING Services, it's SAS and I am looking for help with MDX (SAS documentation on MDX is not elaborate enough as MS MDX and I am new to MDX.

I am using max(measures.members) but that would only give me the maximum of all the measures and I would need a way to compare the maximum value with individual measures across the same dimension to calculate the second highest value.

Any Insights/Suggestions/Functions that would help are greatly appreciated.

Thanks

SAPPER

Answer 1

Hi SAPPER,

Please refer this article to Find Nth Highest Record:

http://pushpendra-sql-ssis.blogspot.com/2011/02/find-nth-highest-record.html

Thanks,
Lola

---

Please remember to mark the replies as answers if they help.

Answer 2

Thanks Lola for the reply,

The paper talks about using SQL to get the Nth largest, but I need help with usng MDX to find the Nth largest among various measures across the same dimension/hierarchy.

-SAPPER