MDX Get MAX MEMBER_KEY from Dimension

Question

I have a very simple SSAS dimension which looks like this:

Dimension Name: DimKeyword

1 Attribute: Keyword with KeywordID as the KeyColumn and Keyword (name) as the NameColumn

Using MDX, I want to get one row with the Max MEMBER_KEY for the DimKeyword dimension (without listing all the Keywords), so far I have:

WITH MEMBER KeywordID as
[Dim Keyword].[Keyword].currentmember.MEMBER_KEY
SELECT {KeywordID} on COLUMNS
FROM [Some Cube]

Although this seems to only return 0 (which I assume is for the member ). Can anyone help?

Thanks!

score 7 · Accepted Answer · answered Sep 21 '12 at 15:41

You can use the max function:

WITH MEMBER [Measures].[Max key] AS
    Max([Dim Keyword].[Keyword].Members, [Dim Keyword].[Keyword].currentmember.MEMBER_KEY)
SELECT {[Measures].[Max key]} on COLUMNS
FROM [Some Cube]

And if you want to have the Keyword member that has the highest MEMBER_KEY, you can use:

TopCount([Dim Keyword].[Keyword].Members, 1, [Dim Keyword].[Keyword].currentmember.MEMBER_KEY).Item(0).Item(0)

MDX Get MAX MEMBER_KEY from Dimension

1 Answers1