MDX-How can I compare members from different dimensions that have the same key values?
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.