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?
- The CubeSetCount function is used to get the number of items in the CubeSet in B2.
- The MakeArray function is then used to create an array with the number of rows returned by CubeSetCount and one column
- 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.
Beating a dead horse with a new stick, I see.
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
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.
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
@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))))
Yes, but you can’t do the same thing with CUBEVALUE though (or couldn’t the last time I checked)
I have a slicer that hides items with no data. Are there any (Cube)formulas that can be used to only display the members in that slicer without having to use rankedmembers? So again, in other words, I need it to be dynamic. Thanks!
Hi,
try to use the MDX function “SUBSET” like this:
=CUBEMEMBER(“ThisWorkbookDataModel”;”Subset([Financials].[Country].Children,0,1)”)
Example:
The data model contains the table ‘Financials’ (it’s the sample dataset of Power BI Desktop), with the column ‘Country’. This column contains 5 elements:
Canada
France
Germany
Mexico
USA
You can get all items of the slicer with the following formulas:
=CUBEMEMBER(“ThisWorkbookDataModel”;”Subset([Financials].[Country].Children,0,1)”)
=CUBEMEMBER(“ThisWorkbookDataModel”;”Subset([Financials].[Country].Children,1,1)”)
=CUBEMEMBER(“ThisWorkbookDataModel”;”Subset([Financials].[Country].Children,2,1)”)
=CUBEMEMBER(“ThisWorkbookDataModel”;”Subset([Financials].[Country].Children,3,1)”)
=CUBEMEMBER(“ThisWorkbookDataModel”;”Subset([Financials].[Country].Children,4,1)”)
Syntax of SUBSET: Subset(Set_Expression, Start [ ,Count ] )
The only difference between these 5 formulas is the start number of the SUBSET function (here: from 0 to 4).
Hint:
The seperators used in the formulas may be different for your excel version.
Regards Frank
Frank – Thank you for the quick reply. Although your cube formula worked well for me, I did have to place a comma between the 2 quotations before the word ‘Subset’. However, it still seems that I have to use a ranking number to retrieve items from the dataset. The slicer I’m trying to use to display the item members is, however, already filtered because I’m ‘hiding items with no data. I was hoping that there might be a formula to link these 2 dimensions (slicers) together so that only the filtered cubemembers are displayed/retrieved. I was looking at your makearray and lambda function, but I couldn’t get it to work for me. My co-worker found something using multiple cubesets and creating a range name for them, then using that range name in a cube function, but so far we’ve had no luck with this idea either. Any other thoughts?
Couple of points:
– this would be an indirect means to access data model data in power query. I just tested it and the data is treated like any other spill formula and is read into PQ.
– this IS slow. Loading a list of 100,000 values takes minutes…even on a relatively quick machine with lots of RAM. Excel locks up ( as is typically the case when the data model is refreshing).