Using a new attribute to separate members on a level

In reporting and analysis, there are situations when a certain member (often named NA, Unknown, or similar) corrupts the analysis. It makes it hard for end users to focus on the rest of the data. It distracts them by making them think what this member represents, why it is here, and why it has data associated to it. Other times, the reason may be that the end users need a total without that member. In both of these situations, they remove that member from the result which generates a new MDX query.

It is true, that a combination of a named set without that member and a calculated member as an aggregate of that set can be created in MDX script to simplify the rest of the calculations and the usage of that hierarchy in general. However, this is not the optimal solution.

Is there a better way? Yes, but it requires a dimension redesign. If that's applicable in your case, read on because this recipe shows how to keep your cube design simple and...