I’ve just read an interesting thread on the SQL Server Developer Center forum, regarding how to filter results. Specifically the difference in MDX between using a subselect
SELECT x on COLUMNS, y on ROWS FROM ( SELECT z on COLUMNS FROM cube))
or using a where clause
SELECT x on COLUMNS, y on ROWS FROM cube WHERE z
In a simple query they produce the same results, but what is the actual difference? You can read the full thread here, but to summarise Darren Gosbell’s response…
Using the WHERE clause sets the query context and consequently the CurrentMember. This then enables functions such as YTD and PerdiodsToDate to work.
Using a subselect can provide improved performance, but does not set the context.