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.

That means that there are no cells available on the worksheet for traditional Excel formulas to reference.

And so traditional Excel formulas cant 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.

How is it that Cube functions retrieve data from the Data Model in the Excel Worksheet Theres no need to worry.

In fact, theres a beautiful collaboration between DAX (measures) and Cube functinos.

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.

Since I dont want to bore you by too much theory, lets finally get our hands dirty.

The tables within the Data Model are connected as illustrated below.

The used tables (shown in bold font) are preceded by a Table Icon with a yellow database icon in the lower right corner.

This yellow symbol indicates the table is part of PowerPivots Data Model.

But from here we can transform the displayed values to Cube 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 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.

Powerpivot Excel For Mac