Creating Cube Functions from Your Data Model – a Flexible Alternative to Calculations in Your Data Model

In the previous chapter, we learned how we could create measures to analyze data using DAX. These measures will be used to create PivotTable reports that will drive some of the key numbers and visuals in our final dashboard. However, there may be situations in our data analysis where we need to get calculations from our data model without using a PivotTable. Cube functions allow us to create flexible calculations to get or retrieve data from our data model so that we can have a more integrated and interactive dashboard.

This chapter covers the use of cube functions in Excel, which are a flexible alternative to calculations in your data model. The chapter will also cover the basics of cube functions and how to use them in your data model, as well as advanced techniques such as using dynamic array formulas and adding slicers to your cube formulas.

We will cover the following...

What are cube functions?

Cube functions are a set of advanced Excel functions that allow you to perform calculations and data analysis from your data model. These functions are designed to work with a data model that is based on a cube, which is a multi-dimensional representation of your data. Cube functions allow you to create flexible calculations and analyses from your data model and express them directly in cells in your worksheet without using PivotTables.

When do we use cube formulas?

Cube formulas should be used when you need more flexibility in your calculations beyond what PivotTables give you. After creating measures, the only way to give expression to the measures in your worksheet is to create PivotTables and drag those measures into the VALUE section of the PivotTable. With cube formulas, your measures do not always have to sit in PivotTables. We can get the measures directly in cells from the data model and use them in calculations in our worksheet.

As an example, if I want to call or see the Total Sales measure we calculated earlier in a cell, we can use CUBEVALUE to retrieve this measure directly from the cube or data model. I used the word “retrieve” because these measures are already calculated and stored by dimensions and hierarchies in the cube. When we use cube formulas, we are essentially using a combination of expressions to retrieve a specific measure or dimension we need from the data model.

We...

Exploring cube functions in Microsoft Excel

There are currently seven cube functions in Excel. We will take a quick look at five of these functions and then proceed to create examples with each function. We will use the data model we created in our earlier chapter.

Of the seven cube functions, three of them are used often and serve as building blocks for the rest. These are CUBEVALUE, CUBESET, and CUBEMEMBER:

Figure 6.7 – List of cube functions in Microsoft Excel

The remaining are CUBERANKEDMEMBER, CUBESETCOUNT, CUBEMEMBERPROPERTY, and CUBEKPIMEMBER.

To help you understand how these cube functions work, it is important to know that all the functions work in a similar way by retrieving aggregated values or attributes from the data model, which we will refer to here as a cube.

Because of this, all the functions start with the same argument, "ThisWorkbookDataModel":

Figure 6.8 – Exploring the syntax...

Spilling cube functions with dynamic array formulas

We have already calculated CUBESETCOUNT formulas that gave us the number of items in each set.

Excel’s SEQUENCE function returns a sequence of numbers when you input the number of rows or columns required. We are going to use the results from our CUBESETSETCOUNT function to indicate the number of rows required. An example is shown next:

Figure 6.35 – Using SEQUENCE to spill numbers

We take advantage of this spilling feature of SEQUENCE and input this formula into our CUBERANKEDMEMBER function so that instead of returning the nth item, it will return all the items in the set based on the numbers in the SEQUENCE formula.

Our completed formula to return all the items in the Regions column looks like this:

Figure 6.36 – Combining SEQUENCE with CUBERANKEDMEMBER to spill cube members

You now know how to create measures using DAX, create summary reports...

Summary

In this chapter, we explored a flexible option to bring or retrieve your measures from your data model using cube formulas. You know that cube formulas are a flexible way to create summary reports for your dashboards when PivotTables do not give you the flexibility you want.

In this chapter, we learned about the key differences between regular PivotTables and data model PivotTables and understood how an MDX query works to retrieve information from our data model.

We explored definitions and examples of five cube functions: CUBEVALUE, CUBEMEMBER, CUBESET, CUBESETCOUNT, and CUBERANKEDMEMBER, and how we can use them in our dashboards. You know now that it is possible to add dynamic array functions to cube functions to make your formula output more dynamic.

We will put all these together to create an interactive dashboard in the next chapter.