SQL Server Analysis Services 2005/2008 articles,blogs,scripts,FAQs,tools,books

Q: How can I compare members from different dimensions that have the same key values?
Lets say I have dimensions [Delivery Date] and [Ship Date]. How can I select just records that were Delivered and Shipped the same day?

A: You can use FILTER function and compare member keys using Properties function:

SELECT {[Measures].[Internet Order Count]} ON 0
, FILTER( NonEmptyCrossJoin( [Ship Date].[Date].Children, [Delivery Date].[Date].Children
)
, [Ship Date].[Date].CurrentMember.Properties('Key')
= [Delivery Date].[Date].Properties('Key')
) ON 1
FROM [Adventure Works]

Result:
Empty result set, as In Adventure DW database there are no records with the same Shipping and Delivery date.

Note: If you want to compare members in the same dimension, but in different hierarchies, you can use LinkMember function.