Get only the maximum value row in MDX

Question

I'm new in MDX. I need to get the the row with MAX(Depth) in the following (Depth is a measure);

Username, Role, Category, Location and Form is dimensions. Name of the Fact table is AccessControl

Username   Role   Category  Location  Form  Depth
John      Creator  Food       NULL     NULL   1
Rick      Creator  Food    Washington  NULL   2

The result I need to get is only Rick's row because it's depth is bigger then John;

   Username   Role   Category  Location   Form   Depth  
    Rick      Creator  Food    Washington  NULL   2

Thank you for your help !

Answer 1

You can use the TopCount function. With the TopCount you can use something like this:

SELECT [Measures].[Depth] ON 0,
TOPCOUNT([Access].MEMBERS, 1, [Measures].[Depth])
ON 1
FROM [AccessControl]