One way we could accomplish this is to mask the data using pre-defined aggregation tables and then use them with the cube, but we really donot want to pre-define the aggregations and are looking at using the MDX.
Also on the OLAP cube it allows adding all the measures using MDX as shown below in one of the calculated Members.
But that only gives me the maximum of all the measures used in the query, the problem now starts with how to loop across all the measures to compare their values with the above calculate maximum measure across the dimensions and hierarchies and to calculate the second largest value of all the measures..
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.