MDX TOP 1 - Return TOP 1 of CUBE

Question

Is there a way to limit the values ​​returned by the MDX query, similar to the SQL TOP 1 query.

SELECT TOP 1 Names From tbl_Names Order by DESC Names

eg. Is it an MDX statement that could do the same?

Answer 1

You can use TOPCOUNT and BOTTOMCOUNT functions. Basically, you specify which dimension or want to set TOP or BOTTOM n, and then specify some numerical or numerical expression with which you can organize the result set.

Answer 2

Try something like:

From the Internet..

WITH SET [TCat] AS TopCount([Product].[Subcategory].[Subcategory],10,[Measures].[Sales Amount]) MEMBER [Product].[Subcategory].[Other] AS Aggregate([Product].[Subcategory].[Subcategory] - TCat) SELECT { [Measures].[Sales Amount] } ON COLUMNS, TCat + [Other] ON ROWS FROM [Adventure Works]