MDX - SQL Server Analysis Services 2012 Cube Development - page 208

ON 1
FROM [Adventure Works]
WHERE [Product].[Product
Categories].[Category].&[2]
2. To reduce the result set based on a specific condition, you can employ the
FILTER function that accepts a set and a condition as parameters. For ex-
ample, the following query returns only those members of the city hierarchy
where sales have exceeded $200,000:
SELECT FILTER([Geography].[City].members,
[Measures].[Reseller Sales Amount] >
200000) ON 0,
[Measures].[Reseller Sales Amount] ON 1
FROM [Adventure Works]
WHERE [Product].[Product
Categories].[Category].[Components]
3. Previous query's result set includes the All Geographies member, which
you might not want to list along with individual cities. Fortunately, we could
use an additional filter criteria exploiting the CurrentMember function to
eliminate the All Geographies member as follows:
SELECT FILTER([Geography].[City].members,
[Measures].[Reseller Sales Amount] >
200000
AND
[Geography].[City].CurrentMember.Name<>
'All Geographies') ON 0,
[Measures].[Reseller Sales Amount] ON 1
FROM [Adventure Works]
WHERE [Product].[Product
Categories].[Category].[Components]