I already wrote about the advantages of using the Data Model in Excel to build the single source of truth for the company data. The Data Model saves time and reduces file size. But its pivot table interaction can limit flexibility. If you're an Excel Pro, you might feel held back. Good news – CUBE functions are the answer!

What are Cube Functions?

The information saved in Power Pivot's Data Model isn't instantly visible on the worksheet. This means that there are no cells on the worksheet that traditional Excel formulas can use. As a result, regular Excel formulas can't access data from the Data Model. Nevertheless, Excel's Cube Functions provide a remedy by allowing users to extract data from designated sources. These functions can efficiently interact with the Data Model in Power Pivot.

The synergy between DAX (measures) and Cube functions is truly remarkable. DAX is essential for conducting calculations on your dataset, with measures stored in a designated Measure within the Data Model. Cube functions, on the other hand, interact with members and retrieve data cube values. A measure defined in DAX becomes a potential member that cube functions can access.

Cube functions act as a bridge between the data cube and the Excel worksheet, referencing members and extracting values. DAX-defined measures are potential members for cube function references. But remember, cube functions aren't substitutes for the DAX formula language, they work hand in hand with it. It's through this synergy that users can access and manipulate data model data effectively.

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

Let's use this data model as an example

You can find a Sales table that’s connected to a Country table (through Client 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 Sales contains a measure with the DAX formula:

Total Sales = SUM( [Sales])

Create a PivotTable using the Data Model

Knowing the data setup, you can now make a pivot showing the Total Sales per Quarter and year.

• Click the tab Insert -> Pivot Table
• Use this Workbook’s Data Model -> OK
• Add Quarter and year to the rows, and the measure Total Sales as Value.

Convert To Formulas

The Pivot Table illustrates the Total Sales arranged by Day Name. Initially, this setup may appear ordinary. However, we can take it a step further by converting the displayed values into Cube formulas. So how to change from a 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.

The newly created cube functions are CUBEMEMBER() and CUBEVALUE().

• 1 a CUBEMEMBER formula that contains a cell reference to the DAX Measure called Total Sales.
• 2 are CUBEMEMBER formulas that contain references to a single member of the data model. In this case, the columns Year and Quarter from the Calendar table filtered on 2020 and Q2.
• 3 is a CUBEMEMBER formula that also references the column Year and Quarter 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 CUBEMEMBER and CUBEVALUE functions are essential for extracting data from the Data Model. While there are other Cube functions available, mastering these two is crucial to begin with. What exactly are the functions of these Cube functions, and how do they operate?

CUBEMEMBER

The CUBEMEMBER function fetches a distinct member from the cube, impacting the result of your CUBEVALUE formula. By verifying the existence of the specified member in the data model or cube, the formula will present this member. If the value is missing, the formula will indicate #N/A.

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

1. Connection -  This is the name of your Data Model. Excel automatically creates it, in this case “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.

CUBEVALUE

The CUBEVALUE function combines linked CUBEMEMBER functions and then provides a summarized value. It serves as a guide for extracting information from the Data Model. The resulting value is determined by two factors. Firstly, it relies on CUBEMEMBER functions to narrow down the Data Model to specific elements, akin to filtering data. Secondly, it hinges on the DAX Measure to specify the calculation to be executed. By intersecting the referenced CUBEMEMBERS, the CUBEVALUE formula carries out the DAX Measure. All the Cube Members act as filters 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.

By leveraging the flexibility of the CUBEVALUE and CUBEMEMBER functions within a robust data model, these formulas offer immense practicality. While a standalone CUBEMEMBER formula showcases a Cube member and an isolated CUBEVALUE formula may seem lacking in value, their real strength emerges when combined. Through harnessing the power of the Data Model, mastering DAX concepts, and utilizing Cube Formulas effectively, you can craft exceptionally powerful Excel reports.