The previous part of our series looking at new tools for working with accounting data in Excel 2010 and 2013 touched on the use of DAX (Data Analysis Expressions) and, in particular, some of the DAX functions that relate to dates and time. As in the previous article, we will use the Invoices data from Microsoft’s sample database: Northwind. [Our thanks to Microsoft for permission to use it in this tutorial. If you are not able to locate a copy of the 434kb zipped you can to download a copy here, then extract Northwind.mdb].
First of all, my apologies if everyone else finds all this stuff blindingly obvious. Hopefully, there will be at least a few people out there who will find the following explanations of some use. We are going to consider some of the concepts underlying the way DAX expressions work as measures within a PivotTable, before looking at the use of some different filter functions in particular.
As we pointed out last time, some of the more complex DAX functions combine Excel calculation functionality with database functionality. Some DAX functions work with columns or whole tables of data as they are arguments rather than just cell references.
Another important concept when using DAX to create measures within a PivotTable is to understand that every cell in a PivotTable behaves like a database query rather than a simple calculation. A database query takes one or more tables of data as the input. One or more criteria can be applied to the input to restrict which rows are included in the output. A value in a PivotTable can be thought of as the result of a single column of a query based on a value field. All the other elements of a PivotTable can be thought of as criteria restricting the rows that are to be totalled in the value column.
Slicers and criteria fields are obviously filters, but all row and column headers are also criteria applied to the cells at each row/column junction. I found that, when working with some DAX functions, it can help to think of each PivotTable value in this way.
Let’s take a (relatively!) simple example. We can use the CALCULATE() function to apply filters to a calculation. The following expression will total the ExtendedPrice field applying the filter that the Country field is equal to ‘Argentina’:
If our PivotTable has no filters of its own, then the expression will take all the values in the ExtendedPrice column and add them up, if the value in the corresponding row of the Country column is equal to Argentina. Here is our resulting PivotTable...