Written By Rick de Groot

Rick is the founder of BI Gorilla. He believes learning is one of life's greatest pleasures and shares his knowledge to help you improve your skills.

Excel-Cube-Functions

In an earlier post, I wrote about the advantages of using the Data Model in Power Pivot. Not only does it save you time by not having to juggle with lookup formulas. It also comes with significant file size savings. That all sounds great. Yet, for many users, the only way to work with data in the Data Model is by using a Pivot Table. A Pivot Table, unfortunately, does not always offer the flexibility spreadsheet developers need. This could prevent Excel Pro’s from using the Data Model. Well, CUBE functions solve that problem!

Table of contents

What are Cube Functions?

Data residing in Power Pivot’s Data Model is not directly visible in the worksheet. That means that there are no cells available on the worksheet for traditional Excel formulas to reference. Thus, traditional Excel formulas can’t retrieve the Data from the Data Model. Yet Cube Functions in Excel are formulas that allow users to retrieve data from certain kinds of sources. Cube functions can interact with the Data Model in Power Pivot.

Now you may say: “Hey, I’ve invested all my time in learning DAX to do calculations on the Data Model. How is it that Cube functions retrieve data from the Data Model in the Excel Worksheet?”

There’s no need to worry. In fact, there’s a beautiful collaboration between DAX (measures) and Cube functions. The DAX language is a necessary component to perform calculations on your dataset. The definition of a measure resides in a so-called Measure and is saved as part of the Data Model.

Cube functions, on the other hand, reference members and get values from the data cube. A measure defined in DAX is one of the possible members that cube functions can reference. You could say that Cube formulas are the portal between the Data Model and the Excel Worksheet. It is through this portal that the Excel user gains access to the Data Model data. Cube functions will never replace the DAX formula language. They instead depend on the measures defined in DAX. Since I don’t want to bore you by too much theory, let’s finally get our hands dirty.

How to use Cube Functions

The easiest way to get started with Cube functions is by using a Pivot Table that uses the Data Model as its Data Source.

Data Setup

Imagine you have the following dataset.

Data Model dataset

You can find a Product Sales table that’s connected to a Gender table and a Calendar table. The tables are part of Excel’s Data Model. The tables within the Data Model are connected as illustrated below.

Relationships in the datamodel

The table Product Sales contains a measure with the DAX formula:

Total Sales = SUM( ‘Product Sales'[Sales])

Create a PivotTable using the Data Model

Knowing the data setup, you can now make a pivot showing the Total Sales per Day Name of the week.

  • Click the tab Insert -> Pivot Table
  • Use this Workbook’s Data Model -> OK
  • Add Day Name to the rows, and the measure Total Sales as Value.
Creating a pivot table based on the data model

Notice that the above Pivot makes use of the Data Model. A Table Icon with a yellow database icon in the lower right corner precedes the used tables (shown in bold font). This yellow symbol indicates the table is part of PowerPivot’s Data Model.

Convert To Formulas

The Pivot Table shows the Total Sales categorized by Day Name. Up to now, there’s nothing special about this setup. But from here, we can transform the displayed values into Cube formulas. So, how do you get from a Pivot Table to Cube formulas?

Convert pivot table to cube formulas
  • Make sure your cursor is within the Pivot Table
  • Click on the contextual tab called Analyze. (Note: this tab only appears when your cursor is within the Pivot Table.)
  • Click OLAP Tools -> select Convert to Formulas

The displayed values in the Pivot Table transform from a Pivot Table to a Cube Formula. And the output is the same.

syntax of cubemember and cubevalue functions

To follow along, you can use this example file:

Take a moment to examine the formulas. The coloured cells on the right contain similar formulas as the coloured cells on the left. The newly created cube functions are CUBEMEMBER() and CUBEVALUE().

  • The orange marked cell is a CUBEMEMBER formula that contains a cell reference to the DAX Measure called Total Sales.
  • The green-marked cells are CUBEMEMBER formulas that contain references to a single member of the data model. In this case, the column Day Name from the Calendar table filtered on Friday.
  • The blue marked cell is a CUBEMEMBER formula that also references the column Day Name in the Calendar table. Yet it references all the values, instead of filtering a single day.
  • The numbers are CUBEVALUE formulas that, in this example, reference two CUBEMEMBER formulas. Notice that the CUBEVALUE formula is the same for each value. It always references the Total Sales Measure and the cube member to the left of it, referencing the day of the week. Without adjustments, the CUBEVALUE formula can easily be copied down.

The functions CUBEMEMBER and CUBEVALUE

The functions CUBEMEMBER and CUBEVALUE play a central role in retrieving data from the Data Model. Other Cube functions exist, yet to get started these two are the most important ones to master. So what’s the purpose of these Cube functions, and how do they work?

CUBEMEMBER

The CUBEMEMBER function returns a member of the cube. It determines what part of the data cube your CUBEVALUE formula returns. The formula verifies whether the specified member exists in the data model/cube; if it does, it will return this member. If the value does not exist, the formula will return #N/A.

The syntax of a CUBEMEMBER formula consists of 3 arguments:
CUBEMEMBER( connection, member_expression, caption)

  1. The first argument is the connection. This refers to the name of your Data Model. Excel automatically creates it, and generally, for me, this is “ThisWorkbookDataModel”.
  2. The Member_Expression comes second. This argument either slices the data cube through certain members or indicates a DAX Measure.
  3. The third argument Caption is optional. If you want your CUBEMEMBER argument to show up with a user-friendly name, this is where to fill this in. This argument is flexible and can contain static text, cell references or formulas.

Some common Cube members are:
Column References (e.g. Day Name or Year in a Calendar Table)
Column References filtered on a single value (e.g. the value Friday in column Day Name in the Calendar Table)
Measures (e.g. Total Sales in the Product Sales table).

Examples:

Cubemember example formulas

CUBEVALUE

The CUBEVALUE function combines the CUBEMEMBER functions it references. Then it returns an aggregated value. You can see it as an instruction on how to retrieve data from the Data Model. Its value depends on two elements. First of all, it depends on CUBEMEMBER functions that slice the Data Model down to specified members. It’s like filtering down your data set. And second, it depends on the DAX Measure that indicates what calculation it should perform. At the intersection of the referenced CUBEMEMBERS, the CUBEVALUE formula performs the DAX Measure. All the Cube Members function as a filter with an AND condition.

The CUBEVALUE formula syntax is:
CUBEVALUE(connection, member_expression1, member_expression2, …)

1. The Connection refers to the Data Model Name.
2. Member Expressions are references to CUBEMEMBER functions. Member expressions that come after the first one are optional. You can optionally add more of these as you require.

Conclusion

By themselves, the CUBEVALUE and CUBEMEMBER functions are not very useful. A single CUBEMEMBER formula can show you a Cube member. And a single CUBEVALUE formula won’t return any value. It is their synergy when working together that is invaluable. By leveraging the Data Model, knowing a little DAX and having Cube Formulas as a tool, you can make incredibly powerful Excel reports.

Share on:
  1. Hi,

    Thanks for the post, really useful as I’m starting to get an understanding of the CUBED functions. Hope you’ll follow it up with a video tutorial as well.

    Thanks

    Reply
  2. Hi,
    Great blog! I’m new to these Cube formulas. Everything is working great except, i can’t seem to get my new lines to append. I’m pulling my data from SQL. To start, I selected a small amount of data to build it. Once i had the layout i wanted. I removed my “Where” clause. The row count in my queries went from 1000 rows to 1800. But my worksheet is still only showing the original 1000 rows. How do i make it show all the rows? Is there somewhere i need to extend the range?

    Thanks,
    Mark

    Reply
    • Hi Mark!

      Using Cubeformulas is great when your setup is pretty much fixed. It provides lots of flexibility. Yet when new data gets added, values that don’t fit in the already existing cube formulas don’t get loaded to the worksheet. You would have to add those manually or convert a new pivot table to formulas.

      Hope that helps,
      Rick

      Reply
  3. Hey Jason!
    I would love to take up the challenge. Could you share it with me through email ?

    Look forward to your email!
    Rick

    Reply
  4. Riddle me this batman. I converted my power pivot table to OLAP cube formulas. Everything worked great. Then I added next month’s data file (with the 12/6/2019 data).

    I wrote out the formula to bring that date in to the column field
    =CUBEMEMBER(“ThisWorkbookDataModel”,”[FTE Accruals Data].[Date].&[2019-12-06T00:00:00]”)

    And it worked! Good so far.

    Then I tried to use cubevalue to bring in the sum of amount for each G/L (like it did for the previous month) and it’s not returning any values for me. It’s just blank.
    =CUBEVALUE(“ThisWorkbookDataModel”,$B$2,$F4,K$3)

    B2 = =CUBEMEMBER(“ThisWorkbookDataModel”,”[Measures].[Sum of Amount 2]”)

    F4 = =CUBEMEMBER(“ThisWorkbookDataModel”,{“[FTE Accruals Data].[Agency or FTE].&[FTE]”,”[FTE Accruals Data].[Business Plan].&”,”[FTE Accruals Data].[Co Code].&”,”[FTE Accruals Data].[Cost Center].&[]”,”[FTE Accruals Data].[GL].&[517987]”})

    K3 = =CUBEMEMBER(“ThisWorkbookDataModel”,”[FTE Accruals Data].[Date].&[2019-12-06T00:00:00]”)

    It should be returning a value for me. I’m a bit dumbfounded, and have spun my wheels on this one.

    If you’d like to see the file I have no problem emailing it to anyone willing to help.

    Reply
  5. You state early in this article that ‘… there’s a beautiful collaboration between DAX (measures) and Cube formulas’. I think this is a misleading statement, whilst Cube formulas can access DAX measures, they can also access implicit, non-DAX, measures. Cube formulas are MDX queries, not DAX, it is possible to add MDX expressions into a CUBE formula to do things you couldn’t do otherwise. Cube formulas existed in Excel 2007, before DAX even existed.

    I would also suggest that to build useful dashboards you need to deploy CUBESET, CUBESETCOUNT and CUBERANKEDMEMBER to get dynamic displays.

    Reply
    • Hi Bob. Thanks for your elaborate reply on this article. Great addition to also use CUBESETCOUNT, CUBERANKEDMEMBER and CUBESET formulas. I haven’t used the CUBESETCOUNT one before but will look into it and perhaps write about it later. What do you use the CUBESETCOUNT formula for? Fun to share?

      In this article I introduced cube formulas. And the CUBEMEMBER and CUBEVALUE formulas are the ones users encounter first. Knowing these, a user can start making some basic reports. Introducing all cube formulas at once may be much for people new to these formulas.

      Also, my experience with MDX is limited and I can only imagine there’s much more one can do using MDX and DAX.

      As I see it, there is a beautiful collaboration between DAX and CUBE formulas. That doesn’t change CUBE formulas can be used for other things too and have been around a long time. What do you mostly use cube formulas for? Would be be great to read in the comments!

      Best regards,
      Rick

      Reply
  6. I haven’t finished reading but I wanted to say this is AWESOME, so far! I think this is my solution to creating better dashboards!

    Reply
    • These formulas are amazing for dashboards. My 2 cents, use as little cube formula’s as possible.

      You could in theory make a dasboard defining all variables in DAX. Like Sales CY (Current Year), Sales LY (Last Year), and have a seperate measure for the difference between these 2. If you already have the Sales CY and Sales TY, I recommend using a normal excel formula to calculate the YoY difference %.

      The reason for this is that each Cube Formula queries the data model, and will use resources. Yet normal excel formulas can calculate with the results of cube formula too. This has far improved the speed of my reports. So less is more for performance reasons!

      Reply

Leave a comment

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