The big news in the world of Excel right now is the introduction of dynamic arrays. They’re only available in the Office 365 click-to-run version of Excel and, at the time of writing, only available to people on the Office Insiders programme, but eventually they’ll be available to anyone running Excel for Office 365 on their desktop. There are already lots of blog posts about them including this overview by Jon Acampora, and you probably also want to download Bill Jelen’s detailed mini-book on them here which is free for the rest of 2018. Now I’m not an Excel expert by any stretch of the imagination but I’m as excited as anyone else about them because they will be incredibly useful for anyone building reports using Excel cube functions against Analysis Services, the Excel Data Model/Power Pivot and Power BI. Bill Jelen’s book has a short section on this subject but the possibilities are limitless…
Here’s one example of how they can be used. A while ago I blogged about how to use a regular array formula and the TextJoin() Excel function to get all the selected items from a slicer. Dynamic arrays make this problem much easier to solve. Take the following table loaded into the Excel Data Model:
Now, say you have a PivotTable built from this and a slicer (called Slicer_Fruit) connected to it:
It’s possible to use the CubeSet() function to get the set of selected items in a slicer using the following formula:
Assuming this formula is in cell H1, you can then get the number of items in this set using CubeSetCount():
Assuming this is in cell H2, all you need to do to get a comma-delimited list of all the selected items in the slicer via this set is:
= TEXTJOIN( ", ", TRUE, CUBERANKEDMEMBER( "ThisWorkbookDataModel", $H$1, SEQUENCE($H$2,1) ) )
Here it is in action:
It works as follows:
- The new Sequence() function is used to create a dynamic array of numbers from one to the number returned by the CubeSetCount() function.
- The CubeRankedMember() gets the name of each of the items in the set using the numbers returned by Sequence()
- TextJoin() then concatenates all of the names returned by CubeRankedMember() into a single comma-delimited list.
You can download the sample workbook here (remember it will only work if you have a version of Excel installed that includes dynamic arrays!).