Spilling cube functions with dynamic array formulas

We have already calculated CUBESETCOUNT formulas that gave us the number of items in each set.

Excel’s SEQUENCE function returns a sequence of numbers when you input the number of rows or columns required. We are going to use the results from our CUBESETSETCOUNT function to indicate the number of rows required. An example is shown next:

Figure 6.35 – Using SEQUENCE to spill numbers

We take advantage of this spilling feature of SEQUENCE and input this formula into our CUBERANKEDMEMBER function so that instead of returning the nth item, it will return all the items in the set based on the numbers in the SEQUENCE formula.

Our completed formula to return all the items in the Regions column looks like this:

Figure 6.36 – Combining SEQUENCE with CUBERANKEDMEMBER to spill cube members

You now know how to create measures using DAX, create summary reports...