linkmember, my favourite MDX function

Linkmember is one of my favourite MDX functions. It tends to be useful whenever you have role playing dimensions. For example, when you have multiple (role playing date dimensions or role playing geography dimensions) such as OrderDate and DespatchDate, or CustomerStore and PurchaseStore.

You might have a report that displays the order date on rows, with sales on columns. You would also like add a column to show sales, but sliced by delivery date. So what you want to do, is put the same, sales, measure on columns, but have the "delivery date" dimension on rows.

This is exactly what the linkmember does. Simply create a calc member that uses linkmember to switch the OrderDate on rows to DeliveryDate. Here is a simple example.

with member
Measures.AmountDelivered as
(Measures.Amount,
linkmember(OrderDate.CalendarYWD,DeliveryDate.CalendarYWD),
root(OrderDate))
select
{Measures.Amount,
Measures.AmountDelivered}
on Columns,
non empty
OrderDate.CalendarYWD.Day on Rows
from MyCube

One thing that you need to remember is that the linkmember will effectively get the member with the same key in another dimension. However, it will not change the original member. So you probably want to also override the original member with a root() or .[All].

Another way of achieving the same functionality as LinkMember is to extract the key and use StrToMember() to construct the appropriate member of the other dimension. However, LinkMember is much more efficient, and is the preferred tool.