MDX - Top N, Order by Dimension and Measure

Question

I am trying to write a fairly-simple (at least in SQL) MDX query, but without success.

Some data insight:

• I am building a TOP 10 Country report
• The top 10 country is bound on the latest chosen year (in this case, 2016)
• If there are no countries in2016, I have to take top 10 from 2015 (only one year back)
• If there are i.e. only 3 countries for 2016, I have to take 7 from 2015

The report is working OK in SQL, but I can't seem to make the same thing in MDX.

What I am able to do (but not sure it's the right way):

• I take Top 10 countries (topcount 10 for one measure) per each year with generate statement
• I order that set by Year dimension (I get 10 countries for each year, sorted by year DESC)

Current script

 SELECT 
    NON EMPTY
    { 
        [Measures].[MeasureX]
    } ON COLUMNS, 

    NON EMPTY
    {
        Generate
        (
            ORDER
            (
                [Date].[Year].[Year].ALLMEMBERS,
                [Date].[Year].CurrentMember.Properties("Member_Value"), 
                DESC
            ),
            { [Date].[Year].CurrentMember } *
            TOPCOUNT
                (
                    [Person].[Country].[Country].MEMBERS, 
                    10, 
                    [Measures].[MeasureX]
                )
        )
    } ON ROWS 
 FROM 
    [Cube]

Which is nearly what I need (but again - probably not the best way). I now get N rows of data, like this (for the sake of the argument, I'm writing like I want to limit it to 5 rows only):

Argentina 1000 2016
Bulgaria 500 2016
USA 444 2016
Germany 8000 2015
Canada 4000 2015
Netherlands 2000 2015

When i put a HEAD (with limit to 5) over my mdx, I get a funky result:

Argentina 1000 2016
Bulgaria 500 2016
USA 444 2016
Germany null 2016
Canada null 2016

What am I missing. What is the right way to go?

EDIT 2017-02-28:

I'm trying to achieve this in OLAP:

;WITH CTE_Subset AS
(
SELECT TOP 50
    YEAR(fis.OrderDate) AS Year,
    SUM(fis.SalesAmount) AS SalesAmount,
    dg.CountryRegionCode
FROM
    dbo.FactInternetSales fis
INNER JOIN
    dbo.DimCustomer dc ON dc.CustomerKey = fis.CustomerKey
INNER JOIN
    dbo.DimGeography dg ON dg.GeographyKey = dc.GeographyKey
GROUP BY
    YEAR(fis.OrderDate),
    dg.CountryRegionCode
ORDER BY
    YEAR(fis.OrderDate) DESC, 
    SUM(fis.SalesAmount) DESC
)
SELECT DISTINCT TOP 10
    CountryRegionCode 
FROM 
    CTE_SubSet 

Answer 1

Using AdvWrks without GENERATE we can do the following:

WITH 
  SET [countryYear] AS 
    Order
    (
      NonEmpty
      (
          [Customer].[Customer Geography].[Country]
        * 
          [Date].[Calendar].[Calendar Year]
       ,[Measures].[Internet Sales Amount]
      )
     ,[Measures].[Internet Sales Amount]
     ,bdesc
    ) 
SELECT 
  [Measures].[Internet Sales Amount] ON 0
 ,Head
  (
    [countryYear]
   ,10
  ) ON 1
FROM [Adventure Works];

It results in the following which is something like your requirement?

We could use a subselect to get the 50 - in AdvWrks there are only 4 distinct countries within the 50:

SELECT 
  {} ON 0
 ,[Customer].[Customer Geography].[Country] ON 1
FROM 
(
  SELECT 
    Head
    (
      Order
      (
        NonEmpty
        (
            [Customer].[Customer Geography].[Country]
          * 
            [Date].[Calendar].[Calendar Year]
         ,[Measures].[Internet Sales Amount]
        )
       ,[Measures].[Internet Sales Amount]
       ,bdesc
      )
     ,50
    ) ON 0
  FROM [Adventure Works]
);

Results in:

Answer 2

I haven't tried the code below, but you may reuse the idea: generate every country using a special measure with year value at the beginning and MeasureX at the end (i.e: 201600001000). 100000000000 may be not enough if you have huge MeasureX values, though.

With
Member [Measures].[MeasureY] as
IIF(
    IsEmpty([Measures].[MeasureX]),
    Null,
    [Date].[Year].CurrentMember.Properties("Member_Value") * 100000000000 + [Measures].[MeasureX]
)

SELECT 
    NON EMPTY
    { 
        [Measures].[MeasureX]
    } ON COLUMNS, 

    NON EMPTY
    {
    TopCount(
        Generate(
            [Person].[Country].[Country].Members,
            TopCount(
                [Date].[Year].[Year].Members, 
                1, 
                [Measures].[MeasureY]
            )
        ),
        10,
        [Measures].[MeasureY]       
    )
    } ON ROWS 
 FROM 
    [Cube]