As we have mentioned before, it’s probably no longer correct to think of Excel as a spreadsheet. It is now a spreadsheet/database hybrid with data manipulation and presentation features being the focus of the majority of recent enhancements and developments. One consequence of this is that the range of functions that were designed to work with cell-based content and calculation are no longer adequate to cope with the type of operations that the data analysis features require Excel to support.

Accordingly, Excel now includes two additional sets of calculation capabilities. Power Query/Get & Transform has its own ‘M’ language for working with data, including performing row-by-row calculations on tables. However, in this short series of articles, we are going to look at the other data-based calculation feature, Data Analysis Expressions (DAX).

DAX is used within the Power Pivot add-in and as part of the modelling section of Power BI. DAX can be used for row-by-row calculations but is perhaps most powerful when used to add Measures to the presentation and visualisation of data.

This first article seeks to explain how DAX works as the foundation for further exploration of what DAX can achieve in part two.

An Excel function by any other name?

There is some good news to start with: many DAX functions are the same as their Excel equivalents. For example, DAY(), MONTH() and YEAR() can all be used to extract part of a complete date using the same syntax as a cell-based Excel formula. Here we can see the use of the three functions entered as row formulae in the Power Pivot window (Figure 1).

Functions with added dimensions

While the three functions discussed above work like normal Excel functions and accept an argument that returns a single value, just like a reference to a single Excel cell, the real power of DAX comes from the ability of many DAX functions to encapsulate entire tables of data within the function.

In the following example, we are calculating the most recent orders that each of our salespeople is responsible for, using a row formula in the Employees table. To do this, we type a formula in the column adjacent to our existing columns to create a new, calculated column. We need to apply a ‘max’ calculation to a list of all the order dates in the Orders table for each employee. We do this by first creating a table of all the rows in the Orders table, where the Orders table EmployeeID matches the EmployeeID of the row in our Employees table. We then extract the OrderDates column from this table to create a list of all order dates for each employee. The max calculation then returns a single value from that list. The actual formula is:

=MAXX(RELATEDTABLE(Orders),Orders[OrderDate])

Unlike MAX() the MAXX() function allows us to use a table and an expression as the function arguments. We use the RELATEDTABLE() DAX function to return all the rows in the entire Orders table according to the database join on EmployeeID for each row of our Employees table. We then extract the single OrderDate column from this table for MAXX() to use to return the highest value (Figure 2).

We can use a PivotTable to demonstrate the way in which this DAX formula works in detail. If we create a PivotTable based on the Power Pivot data model and add Employee LastName as the row label and OrderDate as the Value, we can then set the Summarize Values By option to Max, to get the same results as our DAX formula, but displayed as a PivotTable. If we double-click on the Buchanan value for example, we can create a visualisation of the inner workings of our formula. We see the table that RELATEDTABLE() returns for the Buchanan row, the OrderDate column that is extracted by the second argument of MAXX() and, by sorting Newest to Oldest, confirm the most recent order date that MAXX() returns (Figure 3).

Within the DAX formula, this all goes on in the background, resulting in the return of the single value that we require.

In order to demonstrate the difference between normal Excel formulae and DAX, we created a row formula within a table in the Power Pivot window. The real power of DAX is probably not its use in this way, but rather the ability to create additional measures within PivotTables directly. In the next part of this series of articles we will look at some of the advantages of using measures and what they can add to the capabilities of PivotTables for presenting data.