Cube Functions, the good the bad and how to make them great

I’ve written in the past about using the CUBE functions in Excel.

The general pattern of the examples I’ve used was pretty consistent:

• Creating a set of members of some sort, like products or customers using the CUBESET function
• Extracting the members from the set one by one using the CUBERANKEDMEMBER function
• Using CUBEMEMBER to define which measures I wanted to show from the cube/data model
• Adding some slicers on other properties like product color, year, month, etc.
• Calculating the values of some measures for each extracted member using CUBEVALUE and referring to the cell containing the measures and to the slicer as an argument to the CUBEVALUE function

The CUBESET functions have two optional parameters that allow the set to be ordered. Usually, we order the members in the set on descending order of a measure or a tuple which is a combination of a measure and members from other dimensions that are used to filter the values.

So, if we have a slicer referred to as slicer_Year, we extract the selected year using a formula

=CUBERANKEDMEMBER(“ThisWorkbookDataModel”,slicer_Year,1) in G5

We place the measure in G8 using:

=CUBEMEMBER(“ThisWorkbookDataModel”,”[Measures].[Revenue]”)

And now we can create a tuple in H15 using:

=CUBEMEMBER(“ThisWorkbookDataModel”,($G$5,$G$8))

Notice that the two cells in parentheses define an Excel range, and CUBEMEMBER can receive a range of cells as long as they all contain valid members each in separate dimensions. The result is a tuple, and it can be used to order a set:

=CUBESET(“ThisWorkbookDataModel”,”[Products].[Category].[All].children”,2,$H$15)

Returns a list of categories on descending order of the year selected in slicer_Year and the measure Revenue.

There is one significant flaw in this solution, and for years I couldn’t find a solution for it.

If one or more of the slicers select more than one member, there is no way to create a tuple for the sort order, and so the orders will be wrong. The closest approximation could be to use the first selected member, but this also will not guarantee the right order. If we want to show the top 10 members from a longer list, won’t get the top 10 according to the selection in all slicers.

The solution

A couple of years ago I helped a customer solve a real need based on CUBE functions, and so I had to find a real solution.

You can see in the attached workbook here the full solution to a problem that bugged me for almost ten years.

I abandoned the two optional parameters for ordering a set, and I picked the top N members in the right order using other Excel functions.

In the Report sheet, you see the final result. I show the top 5 cities based on Revenue and values from three slicers and a pivot. A cell used in a pivot table as the filter can be referred to in the same way a slicer can be referred to by CUBE functions like CUBEVALUE and CUBERANKEDMEMBER.

The reason I used a pivot for selecting the year is that at least a pivot filter has a property of allowing single selection and a slicer doesn’t have such default or property.

The report shows the top 5 cities in a selected year and the change in ranking between the year before and the selected year. All this without using DAX except for a simple SUM to define Revenue.

The values are calculated for two product categories and two countries but are still ordered correctly.

The secret sauce

In the Helper sheet, I extracted all members from the cities set.  For each city, I calculate the Revenue based on all slicers and the pivot filter. This works well because CUBEVALUE can use sets as arguments.

Using the Excel RANK function, I calculate the rank of each member in the set.

I add a COUNTIF to the RANK calculation to give cities with equal value for revenue a unique ranking instead of the same value for ranking.

Now I can extract the top N cities using MATCH on the rank and picking ranks 1 to 5 or any other value for top N.

The order is created by the rank function and is always correct.

I had to round the values and also replace null results by 0 for all of this to work.

Is it perfect?

The only disadvantage for this technique could be performance.  In the original method, to show the top 10 members, I extract the first ten members from the set and calculate the values for these 10.  In this solution, I need to extract all members and calculate values for all of them.  If I had thousands or more members, it could be slow to recalculate so many CUBE functions

Microsoft’s platform is the world’s most fluid & powerful data toolset.  Get the most out of it.

No one knows the Power BI ecosystem better than the folks who started the whole thing – us.

Let us guide your organization through the change required to become a data-oriented culture while squeezing every last drop* of value out of your Microsoft platform investment.

* – we reserve the right to substitute Olympic-sized swimming pools of value in place of “drops” at our discretion.