Advanced Topics in MDX - Professional SQL Server Analysis Services 2005 with MDX - page 299

Using EXISTING
By now, you are quite familiar with the WHERE clause in the MDX SELECT
statement. The WHERE clause only changes the default members of the di-
mensions for the current subcube and does not restrict the cube space. It
does not change the default for the outer query and gets a lower precedence
as compared to the calculations specified within the query scope. For ex-
ample, look at the following MDX query:
WITH MEMBER measures.x AS COUNT
([Customer].[Customer Geography].[State-
Province].MEMBERS)
SELECT measures.x ON 0
FROM [Adventure Works]
WHERE ([Customer].[Customer
Geography].[Country].&[United States])
The query returns a value of 71. You know that there are 50 states within the
United States and the count of customers should be <= 50. You get the value
71 because calculations are done at a scope larger than the one defined by
the WHERE clause. In order to restrict the cube space so that calculations
are done within the scope of the conditions specified in the WHERE clause,
you can use several methods. One way to accomplish this is using the
keyword EXISTING, by which you force the calculations to be done on a sub-
cube under consideration by the query rather than the entire cube. Following
is an MDX query using EXISTING:
WITH MEMBER measures.x AS COUNT (
EXISTING [Customer].[Customer
Geography].[State-Province].MEMBERS)
SELECT measures.x ON 0
FROM [Adventure Works]
WHERE ([Customer].[Customer
Geography].[Country].&[United States])
The EXISTING keyword forces sets to be evaluated in the current context.
One can argue that the current context is defined due to the WHERE clause,
which does not actually restrict the cube space. As mentioned earlier we are
using the term "restricting cube space" loosely just to show examples of how
you can restrict the data in a cube to retrieve the results you are looking for.