WHERE Clause Overrides

Each individual set, member, tuple, or numeric function in an MDX statement always executes in the larger context of the entire statement. For example, consider the FILTER function in this expression:

SELECT FILTER(SalesPerson.MEMBERS, [1996].VALUE > 500) ON COLUMNS,
   Quarters.MEMBERS ON ROWS
FROM SalesCube
WHERE ([Geography].[All], [Products].[All], [1996], Sales)

The second argument of FILTER, “[1996].VALUE,” does not contain enough information by itself. You need six coordinates — one from each of the six dimensions — to determine VALUE. The argument contains only one coordinate, from the Years dimension. But in the context of the entire SELECT statement, the other coordinates are available. You can get [Geography].[All], [Products].[All], and [Sales] as three coordinates from the WHERE clause. Two others, one from the SalesPerson dimension and another from the Quarters dimension, are available from the axis expressions for the COLUMNS axis and the ROWS axis, respectively.

A special case arises when a coordinate is specified in both the WHERE clause and within the expression. For example, suppose an application calls for a dataset that, on the COLUMNS axis, contains 1996 budgeted sales for all the states in the United States which had more than 500 units of ActualSales in 1995, and on the ROWS axis, the Quarters. This dataset can be created by the following statement:

SELECT FILTER({USA.CHILDREN}, ([1995], ActualSales) > 500) ON COLUMNS,
   Quarters.MEMBERS ON ROWS
FROM SalesCube
WHERE ([1996], BudgetedSales, [Products].[All], [SalesPerson].[All])

As the FILTER function is evaluated for each state in the United States, it already has the coordinates ([1996], BudgetedSales) from the WHERE clause. However, it receives the coordinates ([1995], ActualSales) from the FILTER function. To avoid potential conflict, the argument of the FILTER function takes precedence. In general, any coordinates obtained from the WHERE clause are overridden by coordinates that are specified within an axis specification.