CODEWITHSUNDEEP
ssasmdx
user3213700
user3213700

Reputation: 159

MDX crossjoin only where members are equal

Is it possible to cross join and return only where two members are equal? For example the following query against the Adventure Works in SSAS 2012

select [Measures].[Reseller Sales Amount] on 0
,topcount(([Ship Date].[Date].Children,[Delivery Date].[Date].Children),5) on 1
from [Adventure Works]

would return the following values

                     Reseller Sales Amount
January 1, 2005 January 1, 2005 (null)
January 1, 2005 January 2, 2005 (null)
January 1, 2005 January 3, 2005 (null)
January 1, 2005 January 4, 2005 (null)
January 1, 2005 January 5, 2005 (null)

What I would like as the result is the following

                     Reseller Sales Amount
January 1, 2005 January 1, 2005 (null)
January 2, 2005 January 2, 2005 (null)
January 3, 2005 January 3, 2005 (null)
January 4, 2005 January 4, 2005 (null)
January 5, 2005 January 5, 2005 (null)

Can I get a result such as the following using an MDX query with or without using a crossjoin?

Upvotes: 0

Views: 227

Answers (1)

The Dumb Radish
The Dumb Radish

Reputation: 896

This should work for you. Basically I create a Boolean style calculated member, and then include that member in the TOPCOUNT function.

I hope it helps.

Ash

WITH MEMBER [Measures].[MatchingDates] AS
IIF([Ship Date].[Date].MEMBERVALUE = [Delivery Date].[Date].MEMBERVALUE,1,0)
SELECT
[Measures].[Reseller Sales Amount] ON 0
,   TOPCOUNT({[Ship Date].[Date].[Date].MEMBERS * [Delivery Date].[Date].[Date].MEMBERS},5,[Measures].[MatchingDates]) ON 1
FROM
[Adventure Works]

Upvotes: 1

Related Questions