SSAS FAQ MDX FAQ MDX-How can I compare members from different dimensions that have the same key values?
MDX-How can I compare members from different dimensions that have the same key values?
User Rating: / 0
PoorBest 
Written by Vidas Matelis   
Saturday, 14 April 2007 09:59

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.

< Prev   Next >
 
Strategy Companion