Multiselect

Multiselects are a source of constant discomfort in SSAS 2005 ff. When there are multiple members in the slice of a MDX query the currentmember function no longer defines a single element and that's the reason for many problems with calculated members. Some of these problems can be solved by the use of special syntax elements (most notably the EXISTING function) but client tools like Excel 2007 generate their own queries so the developer can not control the design (and Excel makes things even worse by using subselects which bring even more annoyance than simple where-clause multiselects).

At least there are some solutions to avoid the most unpleasent consequences - and most of these solutions come from Mosha Pasumansky:

In his blog articles Writing multiselect friendly MDX calculations and Multiselect friendly MDX for calculations looking at current coordinate Mosha Pasumansky shows a way to avoid the multiselect problems by using the function EXISTING and sum up the results. Another solution (using a stored procedure) is shown in How to detect subselect inside MDX calculations (aka Multiselect in Excel 2007) - but Mosha warns "that the solution is neither elegant nor efficient, and probably is mostly of the theoretical interest." In his article I need your help or how to make multiselect work seamlessly in MDX Mosha suggests a solution for all multiselect problems - but it seems he kept that secret...

The blog Korrekte MDX-Berechnungen mit den Analysis Services trotz Multiselect makes the suggestion to define "empty" measures (initially filled with NULL values) and then to overwrite them with scoped assignments on every relevant level - so correct values can be aggregated for higher levels.

All these solutions have an impact on performance, so Mosha writes: "Therefore, it is certainly a choice of cube designer - keep the simpler and more performance friendly expression, but accept that multiselect on the Time dimension will cause errors, or allow more functionality and enable multiselects on Time, but be prepared for slower performance in all cases."