MDX using EXISTING, AGGREGATE, CROSSJOIN and WHERE
It is a well-published approach to using the EXISTING function to decode AGGREGATE members and nested sub-query filters. Mosha wrote a good blog on it here and a more recent one here. The use of EXISTING in these scenarios is very useful and sometimes the only option when dealing with multi-select filters. However, there are some limitations I have run across when using the EXISTING function against an AGGREGATE member:
- The AGGREGATE member must be assigned to the Dimension.Hierarchy being detected by the EXISTING function in the calculated measure.
- The AGGREGATE member cannot contain a crossjoin from any other dimension or hierarchy or EXISTING will not be able to detect the members in the AGGREGATE member.
Take the following query (from Adventure Works DW 2008):
Tags: mdx