Iterating on a set to create a new one

There are situations when we don't want to eliminate certain members from a set, but instead execute for each type of loop. This is done using the Generate() function. The Generate() function applies a set to each member of another set, and then joins the resulting sets by union. In this recipe, we'll show you how to create a new set of members from the existing one.

Getting ready

Let's start a new query in SSMS against the Adventure Works DW 2016 database. Then write the following query:

SELECT 
   NON EMPTY 
   { [Date].[Calendar].[Calendar Year].MEMBERS * 
     [Measures].[Sales Amount] } ON 0, 
   NON EMPTY 
   { [Sales Territory].[Sales Territory Country].MEMBERS } 
   ON 1 
FROM 
   [Adventure Works] 

The query returns four years on the columns and six countries plus the top level, All Sales Territories, on the rows. The result is shown as follows:

Our goal is to get a set of best months, one for each year. We will use the Generate...