Search code examples
ssasmdxolapolap-cube

Calculated member for dimensions

Firstly gonna show you example. We've got a fact table with some id, which is not primary key. Also we have dimension with all ids from fact table and names for that. Our id from fact table is a measure with aggregation function max. Is it possible to create calculated member, which will show name from our dimension using id from fact table? I know that it could be solved using rn and that structure:

Dimension.Hierahchy.Level.Item (meadures.rn).name

But is it possible to solve this another way?

We need to get key for number from measure. Something like that

Dimension.Hierahchy.Level.&[value of measures.maxid]
Solution

  • In mdx you can easily extract a maximum key of a set of members.

    MAX(
Dimension.Hierahchy.Level.MEMBERS,
Dimension.Hierahchy.CurrentMember.MEMBERKEY
)

    (the above is total guesswork as your current question does not include any example of mdx that you have already tried)