Excel Cube Functions, Dynamic Arrays And Lambdas, Part 1: Getting All The Items From A Set

After my recent post on using Office Scripts and cube functions to generate Excel reports from Power BI data, Meagan Longoria asked me this question on Twitter:

To which I can only reply: guilty as charged. I have always loved the Excel cube functions even though they are maybe the least appreciated, least known and least used feature in the whole Microsoft BI stack. They have their issues (including sometimes performance) but they are great for building certain types of report in Excel that can’t be built any other way.

Anyway, the recent addition of new Lambda helper functions to Excel has made me particularly happy because they can be used with cube functions to overcome some limitations that have existed since cube functions were first released in Excel 2007, and to do some other cool things too. In this series of posts I’m going to explore some of the things they make possible.

Let’s start with something simple. In Excel, the CubeSet function can be used to return an (MDX) set of items. This set is stored in a single cell, though, and to extract each item into a cell on your worksheet you need to use the CubeRankedMember function. For example, let’s say I have a table called Sales on my worksheet:

…that is then loaded into the Excel Data Model (aka Power Pivot – although this works exactly the same if I use a Power BI dataset, Azure Analysis Services or SQL Server Analysis Services as my source):

What you can then do is use the CubeSet function to create a set of all the products like so:

=CUBESET("ThisWorkbookDataModel", "[Sales].[Product].[Product].MEMBERS", "Product Set")

…and then use the CubeRankedMember function to put each individual item of the set into a cell. Here’s a simple example worksheet, first with the formulas showing and then the results:

This example shows the fundamental problem that has always existed with CubeRankedMember though: in order to show all the items in a set you need to know how many items there are in advance, and populate as many cells with CubeRankedMember formulas as there are items. In this case see how the range B4:B6 contains the numbers 1, 2 and 3; these numbers are used in the formulas in the range C4:C6 to get the first, second and third items in the set.

If a fourth product was added to the table, however, it would not appear automatically – you would have to add another cell with another CubeRankedMember formula in it manually. I’ve seen some workarounds but they’re a bit hacky and require you to know what the maximum possible number of items in a set could ever be. Indeed that’s always been one of the key differences between cube functions and PivotTables: cube functions are static whereas PivotTables can grow and shrink dynamically when the data changes.

The new MakeArray function in Excel provides a really elegant solution to this problem: you can now write a single formula that returns a dynamic array with all the items in the set in. Assuming that the same CubeSet exists in B2 as shown above, you can do the following:

=MAKEARRAY(CUBESETCOUNT($B$2), 1, LAMBDA(r,c,CUBERANKEDMEMBER("ThisWorkbookDataModel",Sheet3!$B$2,r)))

Here’s the output:

Notice how the formulas in cell B4 returns an array that contains all three items in the set into the range B4:B6.

How does this work?

  1. The CubeSetCount function is used to get the number of items in the CubeSet in B2.
  2. The MakeArray function is then used to create an array with the number of rows returned by CubeSetCount and one column
  3. In the third parameter of MakeArray the Lambda function is used to return a function that wraps CubeRankedMember, which is then called with the current row number of the array

The nice thing about this is that when more products are added to the Sales table they automatically appear in the output of the MakeArray formula in B4. So, for example, with two more products added to the Sales table like so:

Here’s the new output of the formula, showing the two new products returned in the array automatically:

This is not very useful on its own though. In my next post I’ll show you how this can be used to build a simple report.

11 responses

  1. Hi Chris,
    I completely agree with you: the cube functions are probably the least known functions for dynamic evaluations in the BI area. I like to use them very much, even if they still need to be improved. Great that you bring them back into the conversation!

    Here are my comments:
    1.
    When using the MDX function MEMBERS in the function CUBESET() , the element ‘All’ is included, but is not displayed in your screenshot.
    2.
    In addition to the LAMBDA() function, the elements of the cube set can be dynamically returned using the SEQUENCE() array function inside the function CUBERANKEDMEMBER().

    I’m looking forward to your next posts on the Cube features.

    Kind regards Frank

    • Hi Frank, (1) No, that’s not correct – the MDX expression I wrote does not return the All member. I’m asking for [Sales].[Product].[Product].MEMBERS and not [Sales].[Product].MEMBERS (2) Yes, that’s true, but what I’m building up to in my next blog post isn’t possible (or at least wasn’t last year when I tried) because CubeValue can’t be used with the Sequence trick.

      • Hi Chris,
        yes, in retrospect I noticed that the two formulas

        [Sales]. [Product]. [Product]. MEMBERS

        and

        [Sales]. [Product]. CHILDREN

        return the same cube elements.
        For a clearer traceability, I would like to work with the MDX function CHILDREN. Your formula has shown me another possibility to work with cube functions. Thanks for that!

        Kind regards Frank

  2. Maybe I don’t understand the newer technologies, but for me, this concept of PBI/cube formulas will be a game changer. Looking forward to followup posts.

  3. Yes, Excel is making a great come back with the new features. Something that would be truly amazing is Excel Online visual within Power BI

  4. Pingback: Chris Webb's BI Blog: Excel Cube Functions, Dynamic Arrays And Lambdas, Part 2: Returning Tables Chris Webb's BI Blog

  5. Pingback: Fun with Excel Cube Functions – Curated SQL

  6. Pingback: Excel Quick Takes Ten Excel Topics In 3 Minutes - 2419

  7. @Chris – The below works -without needing to use a Lambda and returns a dynamic array of products

    =CUBERANKEDMEMBER(“ThisWorkbookDataModel”,B$2,SEQUENCE(CUBESETCOUNT(B$2)))

    Even better is to use LET do remove dependency on Cell B$2

    =LET(
    vSet,CUBESET(“ThisWorkbookDataModel”,”[PRODUCTS].[PRODUCT].[PRODUCT].MEMBERS”,”ALL”),
    CUBERANKEDMEMBER(“ThisWorkbookDataModel”,vSet,SEQUENCE(CUBESETCOUNT(vSet))))

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: