title: MDX - Top X Sales People by Total Sales for Each Date
body: <p>I'm trying to do this, but with MDX in my cube:</p>
  
  <pre><code>select
      *
  from
  (
      select 
          Date, SalesPerson, TotalSales, row_number() over(partition by Date order by TotalSales desc) as Num
      from SalesFact as ms
  ) as x
  where
      Num &lt; 5
  order by
      Date, SalesPerson, Num desc
  </code></pre>
  
  <p>Let's say I have a cube with these dimensions:</p>
  
  <p>Date (Year, Month, Date) - date is always 1st of month
  SalesPerson</p>
  
  <p>The fact table has three columns - Date, SalesPerson, TotalSales - ie, the amount that person sold in that month.</p>
  
  <p>I want, for each month, to see the top 5 sales people, and each of their TotalSales. The top 5 sales people can be different from one month to the next.</p>
  
  <p>I am able to get the results for one month, using a query that looks like this:</p>
  
  <pre><code>select
        [Measures].[TotalSales] on columns,
        (
              subset
              (
                    order
                    (
                          [SalesPerson].children,
                          [Measures].[TotalSales], 
                          bdesc
                    ),
                    0,
                    5
              )
        ) on rows
  from
        Hypercube
  where 
        (
              [Date].[Date].&amp;[2009-03-01T00:00:00]
        )
  </code></pre>
  
  <p>What I'm after is a query that puts Date and SalesPerson on rows, and TotalSales on columns.</p>
  
  <p>I want to see over time each month, and for each month, the top 5 sales people, and how much they sold.</p>
  
  <p>When I try to do it this way, it doesn't seem to filter / group the sales people by each date (get top 5 for each date). The values returned are all over the place and include very low and null values. Notably, the SalesPerson list is the same for each date, even though TotalSales varies a lot.</p>
  
  <pre><code>select
      [Measures].[TotalSales] on columns,
      (
          [Date].[Hierarchy].[Date].members,
          subset
          (
              order
              (
                  [SalesPerson].children,
                  [Measures].[TotalSales], 
                  bdesc
              ),
              0,
              5
          )
      ) on rows
  from
      Hypercube
  </code></pre>
  
  <p>It seems that everything inside "subset" needs to be filtered by the current [Date].[Hierarchy].[Date], but using CurrentMember gives a crossjoin / axis error:</p>
  
  <pre><code>select
      [Measures].[TotalSales] on columns,
      (
          [Date].[Hierarchy].[Date].members,
          subset
          (
              order
              (
                  ([SalesPerson].children, [Date].[Hierarchy].CurrentMember),
                  [Measures].[TotalSales], 
                  bdesc
              ),
              0,
              5
          )
      ) on rows
  from
      Hypercube
  </code></pre>
  
  <blockquote>
    <p>Error:     Executing the query ... Query (3, 2) The Hierarchy hierarchy
    is used more than once in the Crossjoin function.</p>
    
    <p>Execution complete</p>
  </blockquote>
  
  <p>I've tried several variations of the last query with no luck.   </p>
  
  <p>Hopefully the answers will be helpful to others new to MDX as well.</p>