skip to Main Content

Cube Functions banner

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.

Top 5 cities

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.

Dany Hoter

Dany Hoter has been using Microsoft BI tools, especially Excel, since 1998. He was part of the Excel and Power BI teams in different positions for most of this time.

Dany lives in Jerusalem, Israel and shares his time enjoying his grandkids (6 of them), teaching Excel and Power BI, and writing articles.

This Post Has 3 Comments
  1. I had an Excel file with several sheets and hundreds of cube formulas. I loved the flexibility of being able to use variables and cell references to filter measures, and place results exactly where desired in cells. On the other hand, I struggled with learning yet another language, that of cube formulas.

    Refreshing always seemed to be relatively intensive and somewhat long. After a year I learned that each cube formula was actually a distinct query against the data model. 500 cube formulas = 500 queries. Ouch!!

    An alternative approach was suggested by the world renowned Marco Russo of sqlbi. Create one or more Pivot Tables. Each Pivot Table was a single query against the data model, even if they include many levels of rows and columns, and several measures. 10 Pivot Tables = 10 queries. Replace all the cube formulas with =GETPIVOTDATA formulas pointing to the Pivot Tables. PURE GENIUS! Refresh is much faster. Plus, referencing a measure is a simply point-and-click on the value in the Pivot Table. Far easier than writing cube formula.

  2. It seems that a full comparison between GetPivotData and the cube functions is worth it’s own article.

    For now a few comments:
    – GetPivotData is a great solution in some cases but as always there are pros and cons
    – It is very difficult to write to write GetPivotData from scratch manually and usually you start by clicking a value in a pivot tale. The problem with this method is that the generated formula contain just constants and will not adjust if you drag it down or across.
    – There is no equivalent to cubemember or cubeset/cuberankedmember, getpivotdata is equivalent in a way to cubevalue
    – Any change in the pivot like expanding or bringing another field might break all you references to non/measure values in the pivot.
    – 500 cubevalue formulas do not generate 500 queries. It generates much less queries unless each formula point to a different set of measures/dimensions and there is no way to bring multiple values in one query. How many are combined depends on the total length of the MDX query which is a bit difficult to predict because it depends on weird factors like the length of the measure names.
    – The values returned by cubefunctions are cached and until the model changes, the next time the same value is retrieved it will be brought from cache.

  3. Hi, I would love to read about recommender systems in PowerPivot.

    How to make PowerPivot recommend items based on other recomendations just like Amazon recommends new products or similar items.

Leave a Comment or Question