A step to MDX

SELECT

 {[Measures].[Internet Sales Amount]} ON COLUMNS

FROM [Adventure Works]

WHERE [Product].[Subcategory].[Helmets]

SELECT

 {[Measures].[Internet Sales Amount]} ON COLUMNS

 ,[Ship Date].[Day of Week].MEMBERS ON ROWS

FROM [Adventure Works]

WHERE [Product].[Subcategory].[Helmets]

SELECT

 {[Measures].[Internet Sales Amount]} ON COLUMNS

 ,[Product].[Color].MEMBERS ON ROWS

FROM [Adventure Works]

WHERE [Product].[Subcategory].[Helmets]

SELECT Exists([Product].[Color].MEMBERS,[Product].[Subcategory].[Helmets]) ON COLUMNS, {} ON ROWS

FROM [Adventure Works]

SELECT

 {[Measures].[Internet Sales Amount]} ON COLUMNS

 ,Descendants([Product].[Product Model Categories].[All Products], [Product].[Product Model Categories].[Category], SELF_AND_BEFORE) ON ROWS

FROM [Adventure Works]

WHERE [Product].[Subcategory].[Helmets]

WITH MEMBER Measures.RatioToParent

  AS [Measures].[Internet Sales Amount] / ([Measures].[Internet Sales Amount],[Product].[Product Model Categories].Parent)

  , FORMAT_STRING = 'Percent'

SELECT Measures.RatioToParent ON COLUMNS

FROM [Adventure Works]

WHERE [Product].[Subcategory].[Helmets]

SELECT

 {[Measures].[Internet Sales Amount]} ON COLUMNS

 ,[Product].[Category].MEMBERS ON ROWS

FROM [Adventure Works]

WHERE [Product].[Subcategory].[Helmets]

WITH MEMBER Measures.CapsSales AS ([Measures].[Internet Sales Amount],[Product].[Product Categories].[Caps])

SELECT Measures.CapsSales ON COLUMNS

FROM [Adventure Works]

WHERE [Product].[Subcategory].[Helmets]

SELECT ([Measures].[Internet Sales Amount],[Product].[Product Categories].[Caps]) ON COLUMNS

FROM [Adventure Works]

WHERE [Product].[Subcategory].[Helmets]

SELECT

 {[Measures].[Internet Sales Amount]} ON COLUMNS

 ,Descendants([Product].[Product Model Categories].[All Products], [Product].[Product Model Categories].[Category], SELF_AND_BEFORE) ON ROWS

FROM [Adventure Works]

WHERE [Product].[Subcategory].[Helmets]

Descendants

Flag	Description
SELF	Returns only descendant members from the specified level or at the specified distance. The function includes the specified member, if the specified level is the level of the specified member.
AFTER	Returns descendant members from all levels subordinate to the specified level or distance.
BEFORE	Returns descendant members from all levels between the specified member and the specified level, or at the specified distance. It includes the specified member, but does not include members from the specified level or distance.
BEFORE_AND_AFTER	Returns descendant members from all levels subordinate to the level of the specified member. It includes the specified member, but does not include members from the specified level or at the specified distance.
SELF_AND_AFTER	Returns descendant members from the specified level or at the specified distance and all levels subordinate to the specified level, or at the specified distance.
SELF_AND_BEFORE	Returns descendant members from the specified level or at the specified distance, and from all levels between the specified member and the specified level, or at the specified distance, including the specified member.
SELF_BEFORE_AFTER	Returns descendant members from all levels subordinate to the level of the specified member, and includes the specified member.
LEAVES	Returns leaf descendant members between the specified member and the specified level, or at the specified distance.

SELECT

 {[Measures].[Internet Sales Amount]} ON COLUMNS

 ,[Product].[Category].MEMBERS ON ROWS

FROM [Adventure Works]

WHERE [Product].[Subcategory].[Helmets]

SELECT {} ON 0

, [Product].[Product].[Product] ON 1

FROM [Adventure Works]

WHERE [Product].[Subcategory].[Pedals]

SELECT {} ON 0

, [Product].[Product].[Product] ON 1

FROM [Adventure Works]

WHERE [Product].[Subcategory].[Pedals]

Even though we asked for all products on rows, we are only getting 7 products in the resulset - all products from the Pedals subcategory. The reason is an implicit Exists between rows axis and slicer axis - i.e. rows actually returned

Exists([Product].[Product].[Product], [Product].[Subcategory].[Pedals])

This seems to be logical and good, but let's try now something a little bit more complex. Let's suppose that we are analyzing our heaviest products (perhaps because they cost us extra shipping fees). The query to look at 5 heaviest products would be

WITH MEMBER Measures.ProductWeight AS Product.Weight.CurrentMember.MemberValue

SELECT 

Measures.ProductWeight on 0

,TopCount([Product].[Product].[Product],5,Measures.ProductWeight) on 1

FROM [Adventure Works]

We got the top 5 products by weight - turns out that Rear and Front Wheels can be really heavy (In fact they are much heavier then the whole bike for some reason! Perhaps there are data quality problems in Adventure Works ?). Now we want to focus more on the Pedals subcategory, so we naturally change the query to

WITH MEMBER Measures.ProductWeight AS Product.Weight.CurrentMember.MemberValue

SELECT 

Measures.ProductWeight on 0

,TopCount([Product].[Product].[Product],5,Measures.ProductWeight) on 1

FROM [Adventure Works]

WHERE [Product].[Subcategory].[Pedals]

But this time, the result comes empty ! This looks really wrong. Well, the result can be explained - if we do implicit Exists between top level axis set and slicer axis, then we would get

Exists(TopCount([Product].[Product].[Product],5,Measures.ProductWeight), [Product].[Subcategory].[Pedals] )

And since we already saw that top 5 heaviest products are all wheels - none of them exists with Pedals subcategory - so the result is empty. This explanation might sound reasonable, but it doesn't help users - whatever the technical details of the design is - the result is counterintuitive, and the users will consider it as a bug. Especially, after we notice, that subselects behave really good in the very same scenario:

WITH MEMBER Measures.ProductWeight AS Product.Weight.CurrentMember.MemberValue

SELECT 

Measures.ProductWeight on 0

,TopCount([Product].[Product].[Product],5,Measures.ProductWeight) on 1

FROM 

(SELECT [Product].[Subcategory].[Pedals] ON 0

 FROM [Adventure Works])

We get, as expected, top 5 pedal products by weight. Turns out, that subselects apply implicit Exists in a smarter way then WHERE clause. Subselects apply Exists not to the end result of the axis, but they can push it deeper, depending on the semantics of the MDX functions used to construct the set. I.e. instead of doing

Exists(TopCount([Product].[Product].[Product],5,Measures.ProductWeight), [Product].[Subcategory].[Pedals] )

subselects will actually do

TopCount(Exists([Product].[Product].[Product],[Product].[Subcategory].[Pedals]),5,Measures.ProductWeight)

For functions like Filter or Order it doesn't matter whether to do Exists before or after applying the function since the results are going to be the same, but it does matter for performance - the deeper Exists is pushed - the better performance is likely to be. Of course, query optimizer takes all this into consideration. And it will work correctly when there are as many nested functions as you want - i.e.

WITH MEMBER Measures.ProductWeight AS Product.Weight.CurrentMember.MemberValue

SELECT 

Measures.ProductWeight on 0

,Filter(TopCount([Product].[Product].[Product],5,Measures.ProductWeight), ProductWeight > 170) on 1

FROM (SELECT [Product].[Subcategory].[Pedals] ON 0

FROM [Adventure Works])

Exists is going to be pushed to the deepest nested call here - i.e. into TopCount.

So it appears that subselect does the right thing, but WHERE clause doesn't, and, as we noted, most client tools do use WHERE and not subselects. Therefore, SP2 introduced additional change, to make implicit Exists between WHERE and axes to go as deep as subselect does - therefore the following query on SP2 returns good results

WITH MEMBER Measures.ProductWeight AS Product.Weight.CurrentMember.MemberValue

SELECT 

Measures.ProductWeight on 0

,TopCount([Product].[Product].[Product],5,Measures.ProductWeight) on 1

FROM [Adventure Works]

WHERE [Product].[Subcategory].[Pedals]