Report Portal

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.

Tags: mdx, faq

 

2007-2015 VidasSoft Systems Inc.