MDX calculated member using LinkMember and currentmember not showing correctly at different hierarchies

Question

I am new to SSAS and cubing and am running into trouble with a linkedmember function. I have created a cube with three dates in the fact table (basically accumulating snapshot granularity). Each row represents a client, then there is a startdate, enddate, and firstvisit date. Each date has a corresponding dimension connected to the dates table through role playing members. I am hoping to use the dates dimension that is connected to startdate as the primary dates dimension for users. I was able to use the following function to create a calculated member for active clients.

AGGREGATE( 
      {NULL:[Dates].[Year -  Quarter -  Month -  Date].currentmember}
    * {LINKMEMBER([Dates].[Year -  Quarter -  Month -  Date].currentmember,[EndDate].[Year -  Quarter -  Month -  Date]):NULL}
    , [Measures].[ClientCount])

I'm now working on creating calculated measure for clients with their firstvisit during the time period. I tried this equation:

(Measures.clientcount,ROOT(dates), 
     linkmember(dates.[Year -  Quarter -  Month -  Date].currentmember,
             [FirstVisit].[Year -  Quarter -  Month -  Date])
)

It is correct at the year level, however there are errors at the month level. For January it shows the sum of the year, then each month starting a quarter it shows the sum of the quarter. It is correct for the other months (such as feburary and march). My understanding is that this is caused by the currentmember only evaluating once or evaluating for All. Is there a work around for this?

user2938189 user2938189 · Accepted Answer · 2013-11-04T17:59:49

Finally found a workaround.

MEMBER Measures.NewlyEnrolled AS
    (Measures.clientcount,root(Dates),LINKMEMBER([Dates].[Year -  Quarter -  Month -  Date].currentmember,
    CASE 
        WHEN [Dates].[Year -  Quarter -  Month -  Date].currentmember.level is [Dates].[Year -  Quarter -  Month -  Date].[Year]
        THEN [FirstVisit].[Year]            
        WHEN [Dates].[Year -  Quarter -  Month -  Date].currentmember.level is [Dates].[Year -  Quarter -  Month -  Date].[Quarter]
        THEN [FirstVisit].[Quarter] 
        WHEN [Dates].[Year -  Quarter -  Month -  Date].currentmember.level is [Dates].[Year -  Quarter -  Month -  Date].[Month]
        THEN [FirstVisit].[Month] 
        WHEN [Dates].[Year -  Quarter -  Month -  Date].currentmember.level is [Dates].[Year -  Quarter -  Month -  Date].[Day]
        THEN [FirstVisit].[Day] 
    END ))

MDX calculated member using LinkMember and currentmember not showing correctly at different hierarchies

1 Answers