Вопрос по MDX - использование Top Count с функцией Rank/Order

Question

Как использовать верхний счет, чтобы он возвращал только первые 2 записи с самыми высокими продажами для каждой группы территории продаж (Северная Америка, Тихий океан и т. Д.)

WITH 
   MEMBER [Measures].[Group_Ranking_InternetSales] AS
   RANK( [Sales Territory].[Sales Territory Group].CurrentMember,
   ORDER( [Sales Territory].[Sales Territory Group].[Sales Territory Group].Members , [Measures].[Internet Sales Amount], BDESC)
   ) 

   MEMBER [Measures].[Group_Territory_Ranking_InternetSales] AS
   RANK(( [Sales Territory].[Sales Territory Group].CurrentMember,   [Sales Territory].[Sales Territory Country].CurrentMember),
   ORDER( CROSSJOIN([Sales Territory].[Sales Territory Group].CurrentMember,  [Sales Territory].[Sales Territory Country].[Sales Territory Country]) , [Measures].[Internet Sales Amount], BDESC)
   ) 
SELECT 
      {
   [Measures].[Group_Ranking_InternetSales]
   ,[Measures].[Group_Territory_Ranking_InternetSales] 
   , [Measures].[Internet Sales Amount]
      } ON COLUMNS,
   (
           ORDER  ([Sales Territory].[Sales Territory Group].[Sales Territory Group], [Measures].[Group_Ranking_InternetSales] , BASC ) 
   ,ORDER  ([Sales Territory].[Sales Territory Country].[Sales Territory Country], [Measures].[Group_Territory_Ranking_InternetSales] , BASC ) 
   )
   ON ROWS 
FROM
      [Adventure Works]

Конечный результат должен состоять в том, что он удаляет выделенную строку ниже.

Answer 1

Попробуй сейчас

                WITH 
            MEMBER [Measures].[Group_Ranking_InternetSales] AS
            RANK( [Sales Territory].[Sales Territory Group].CurrentMember,
            ORDER( [Sales Territory].[Sales Territory Group].[Sales Territory Group].Members , [Measures].[Internet Sales Amount], BDESC)
            ) 

            MEMBER [Measures].[Group_Territory_Ranking_InternetSales] AS
            RANK(( [Sales Territory].[Sales Territory Group].CurrentMember,   [Sales Territory].[Sales Territory Country].CurrentMember),
            ORDER( CROSSJOIN([Sales Territory].[Sales Territory Group].CurrentMember,  [Sales Territory].[Sales Territory Country].[Sales Territory Country]) , [Measures].[Internet Sales Amount], BDESC)
            ) 
            SELECT 
            {
            [Measures].[Group_Ranking_InternetSales]
            ,[Measures].[Group_Territory_Ranking_InternetSales] 
            , [Measures].[Internet Sales Amount]
            } ON COLUMNS,
            filter(///Added this
            (
            ORDER  ([Sales Territory].[Sales Territory Group].[Sales Territory Group], [Measures].[Group_Ranking_InternetSales] , BASC ) 
            ,ORDER  ([Sales Territory].[Sales Territory Country].[Sales Territory Country], [Measures].[Group_Territory_Ranking_InternetSales] , BASC ) 
            )
            ,[Measures].[Group_Territory_Ranking_InternetSales]<3) ///Added this
            ON ROWS 
            FROM
            [Adventure Works]