Exists and Filter don't play together?

Can anybody explain why the first two queries work, but the last query returns no rows?

--works fine

with

member [Measures].[TestTrue] as True

select {[Measures].[Internet Sales Amount]} on 0,

Exists(

[Product].[Product].[Product].members

,

,"Internet Sales"

)

on 1

from [Adventure Works]

--works fine

with

member [Measures].[TestTrue] as True

select {[Measures].[Internet Sales Amount]} on 0,

Filter([Product].[Product].[Product].members, [Measures].[TestTrue])

on 1

from [Adventure Works]

--DOES NOT WORK! RETURNS NO ROWS

with

member [Measures].[TestTrue] as True

select {[Measures].[Internet Sales Amount]} on 0,

Exists(

Filter([Product].[Product].[Product].members, [Measures].[TestTrue])

,

,"Internet Sales"

)

on 1

from [Adventure Works]

This does look like a bug. The fact that calculated member is used inside Filter, seem to somehow impact Exists (perhaps calculated member stays in the context?). So it's the culprit. Otherwise, Exists and Filter do coexits - for example the following works fine.

with

member [Measures].[TestTrue] as True

select {[Measures].[Internet Sales Amount]} on 0,

Exists(

Filter([Product].[Product].[Product].members, 1>0)

,

,"Internet Sales"

)

on 1

from [Adventure Works]

You probably need to open case with PSS about this bug to get investigation going.

|||

Thanks for confirming my suspicions, Mosha. I just reported this to connect:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=251425

This bug isn't holding me back at all since I can just replace the Exists with a NonEmpty and it works:

NonEmpty(
Filter([Product].[Product].[Product].members, [Measures].[TestTrue])
,[Measures].[Internet Sales Amount]
)

|||

Please note, that there are couple of semantic difference between Exists(set, measuregroupname) and NonEmpty(set, measure).

1. If you have any calculations applying to (set,measure) intersection, NonEmpty will take them into account, but Exists won't

2. If you have nullable measures in the measuregroup, the rows where measure is NULL are filtered away for NonEmpty, but not for Exists.