MDX join with the same dimension

Question

I'm writing some MDX to join a dimension to itself based on two different periods to get a common list, then do a count against this list for both.

In short, I need to

• get a list of Student.UniqueId's for Period1 which has a flag (IsValid) that is set that isn't set within the Period2 data
• get a full list of Students for Period2
• join the two lists and produce two records (one for each period) with the same count (these counts will be used for calculated member calculations within each period)

I have tried doing it via subselect and exists clause with filter

SELECT 
{   
    [Measures].[FactStudentCount]
}  on COLUMNS,
{ NONEMPTY
    (
        [TestEvent].[TestEvents].[Name].ALLMEMBERS
        * [TestEvent].[PeriodName].[PeriodName].ALLMEMBERS 
    )                            
}  ON ROWS
FROM ( SELECT ( { 
    exists
    (
        filter([Student].[UniqueId].[UniqueId].MEMBERS
            ,([TestEvent].[Key].&[Period1], [IsValid].[Code].&[Yes]))
        ,
        filter([Student].[UniqueId].[UniqueId].MEMBERS
            ,[TestEvent].[Key].&[Period2])
    )
    }) ON COLUMNS
FROM [MyCube])

...however this doesn't give the correct result

(To obtain context) I have also tried similar exists/filter within a where clause

SELECT 
{   
    [Measures].[FactStudentCount]
}  on COLUMNS,
{ NONEMPTY
    (
        [TestEvent].[TestEvents].[Name].ALLMEMBERS
        * [TestEvent].[PeriodName].[PeriodName].ALLMEMBERS 
    )                            
}  ON ROWS
FROM [MyCube]
where (
    exists
    (
        filter([Student].[UniqueId].[UniqueId].MEMBERS
            ,([TestEvent].[Key].&[Period1], [IsValid].[Code].&[Yes]))
        ,
        filter([Student].[UniqueId].[UniqueId].MEMBERS
            ,[TestEvent].[Key].&[Period2])
    )
)

...however again this doesn't produce the correct result

I have tried tweaking the filter statements (within the exists) to something like

(filter(existing([Student].[UniqueId].[UniqueId].allmembers),[TestEvent].[Key].CurrentMember.MemberValue = 'Period1'), [IsValid].[Code].&[Yes])
,
(filter(existing([Student].[UniqueId].[UniqueId].allmembers),[TestEvent].[Key].CurrentMember.MemberValue = 'Period2'))

...however this only returns one row (for Period1) - that said it is the correct total

I have also tried via a CrossJoin with NonEmpty however it fails because the fields come from the same hierarchy - the message "The Key hierarchy is used more than once in the Crossjoin function"

Does any one have any insight into how to resolve the above scenario ?

Answer 1

This is what I did

NonEmpty(
    NonEmpty(
        {([Student].[UniqueId].[UniqueId].members)},{([TestEvent].[Key].&[Period1], [IsValid].[Code].&[Yes])}
    )
    ,
    {([Student].[UniqueId].[UniqueId].members,[TestEvent].[Key].&[Period2])}
)

This gets all Period1 elements, with IsValid='Yes' then 'left joins' this with records in Period2