MDX Calculated Member Third Parameter

Question

I'm working within a self-serve BI tool (Pyramid Analytics, if that helps), which allows use of custom members using MDX.

I am trying to locate a measure connected to a different dimension (Forecast Amount tied to Forecast Week) based on the value selected in the selected dimension, Current Order Date.

The linkmember function is what I thought of using and is incidentally currently being used. The guy that worked before me, came up with this query for the calculated member:

<code>
    (
        LINKMEMBER
        ( 
            [Order Date].[Order Calendar].[!@Current Order Date@!].PARENT
            , [Forecast Date].[Order Calendar]
        )
        ,[Measures].[Forecast Amount]
        ,[Order Date].[Order Calendar].[All]
    )
</code>

I am now tasked with making more changes to this query (different measure and dimensions etc.)

My question was to do with the third argument/parameter in the above overall query, [Order Date].[Order Calendar.[All], (not the linkmember bit, which I know how it works).

My interpretation of the above statement in pseudo-english was, "for the member or set of members generated by the first argument (LINKMEMBER statement), return the corresponding measure amount (Forecast Amount)" but for the life of me, I can't figure out what the third argument does and whether it is superfluous. The query does work in its current form so whether I drop it or not, I'd like to know why I'm doing so.

I tried searching to see if this has something to do with the scope of the query but it doesn't include the SCOPE keyword and have not found any search results on the "third parameter" etc. To my knowledge, this isn't related to sub-cubes etc, but I'm happy to be corrected.

Any inputs or pointers into what I should be looking for would be much appreciated.

(apologies if I've missed some other search criteria)

Thanks, S

Edit added to add example while testing FrankPI's answer

Thanks FrankPI. Took a while to digest but got it eventually by reading your comment and coming up with an example.

If I understand correctly, without the third parameter, the Measure returned as a result of the first 2 arguments is then sub-setted (/sliced) by whatever appears in the current row like so. If there is a relationship between the two, then its fine.

<code>
    with 
    member [measures].[x1] as
        (
            LINKMEMBER
            ( 
                [Order Date].[Order Calendar].[Trading Week].&[2014]&[47]
                , [Forecast Date].[Order Calendar]
            )
            ,
            [Measures].[Forecast Amount]
        )
    select 
    {
        [measures].[x1]
    }
    on 0,
    {
        [Forecast Date].[Order Calendar].[Trading Week].&[2014]&[47]
    }
    on 1
    from [Cube]
</code>

But when the below happens (no relationship), then Null is returned unless the context is established, like so

<code>
    with 
    member [measures].[y1] as
        sum
        (
            LINKMEMBER
            ( 
                [Order Date].[Order Calendar].currentmember
                , [Forecast Date].[Order Calendar]
            )
            ,
            [Measures].[Forecast Amount]
        )
    member [measures].[y2] as
        (
            LINKMEMBER
            ( 
                [Order Date].[Order Calendar].currentmember
                , [Forecast Date].[Order Calendar]
            )
            ,
            [Measures].[Forecast Amount]
            ,
            [Order Date].[Order Calendar].[All]
        )
    select 
    {
        [measures].[y1] // returns null
        ,
        [measures].[y2] // returns value
    }
    on 0,
    {
        [Order Date].[Order Calendar].[Trading Week].&[2014]&[47]
    }
    on 1
    from [Cube]
</code>

Thanks for the answer!

FrankPl FrankPl · Accepted Answer · 2014-11-13T12:21:34

The third parameter changes the context of the Order Calendar hierarchy to use all order dates, instead of just the current one.

LinkMember changes the Forecast Date current member for which the calculation takes place, but does not change the Order Calendar currentMember from whatever comes from the environment (e. g. current row/column, WHERE condition).

MDX Calculated Member Third Parameter

1 Answers