In an earlier post I wrote about the advantages of using the Data Model in Powerpivot. 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 Formulas solve that problem!
What are Cube Formulas?
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. And so traditional Excel formulas can’t retrieve the Data from the Data Model. Yet Cube Formulas in Excel are formulas that allow users to retrieve data from certain kinds of sources. Cube formulas 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 formulas 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 formulas. 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 formulas 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 formulas 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 Formulas 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 Formulas
The easiest way to get started with Cube formulas is by using a Pivot Table that uses the Data Model as its Data Source.
Imagine you have the following 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.
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
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 to Cube formulas. So how do you get from a Pivot Table to Cube formulas?
- Make sure your cursor is within the Pivot Table
- Click on the the contextual tab called Analyze. (Note: this tab only appears when you 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 Formulas. And the output is the same.
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 formulas 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.
CUBEMEMBER() and CUBEVALUE()
The functions CUBEMEMBER() and CUBEVALUE() play a central role in retrieving data from the Data Model. Other Cube formulas exist, yet these two are the most important ones to master. So what’s the purpose of these Cube formulas and how do they work?
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 and 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)
- 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”.
- The Member_Expression comes second. This argument either slices the data cube through certain members, or indicates a DAX Measure.
- 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 very 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).
The CUBEVALUE function combines the CUBEMEMBER formulas 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 formulas 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 functoin 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 formulas. Member expressions that come after the first one are optional. You can optionally add more of these as you require.
By themselves, the CUBEVALUE and CUBEMEMBER formulas 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.