Context is King

If you’re new to Power Pivot (especially if you have an Excel background) it’s very beneficial to study Power Pivot context before trying to learn DAX.  The context provided by Power Pivot enables the creation of dynamic calculations that respond to user selections.

It’s not hard, it’s just different to what your probably used to.

There are 2 types of contexts in Power Pivot.

1. Filter Context

The Filter Context determines which rows of the table are selected in the calculation.

Filter example 1

In the example above the filter is set by the pivot table.  The selections on the slicers will determine the filter along with the actual row and column headings of the pivot table.

We can expand the filter context by control and clicking on an extra Product Brand :

Filter example 2

The reason that the filter context is important with DAX is due to the fact we cannot refer to cells, columns, rows like we can with Excel.

Instead, we can programmatically control the filter context using DAX formulas. This is far better than the old way of referencing cells you might be used to with Excel such as : A1:A10.  With DAX you don’t have to worry about the order of rows or receiving more rows than the formula was designed for (a very common spreadsheet error!)

When building DAX expressions you should start to think about which rows you want to filter in order to calculate your result.   You probably do this already but it’s intuitive so you don’t really think about it.   With DAX, at the beginning you’ll find it easier to write expressions if you think more clearly about filtering.

2. Row Context

In Power Pivot the table of data consists of Columns and Rows.  There are no Cells!  If we create a formula in a calculated column, the row context for that formula will include the values from all columns in the current row. If the table is related to another table, the row context is also extended to the second table for the related rows.

The row context simple returns 1 or more rows.   In DAX we can write functions that iterate calculations over the table.   Additionally we can write nested formulas that create nested row contexts.  This can be conceptually hard to understand for an Excel user.  It’s best to imagine an outer loop and an inner loop with each loop representing a loop through the rows of the table.   When in the inner loop we can refer to the outer loop to perform very creative calculations.

Good luck on your journey to learning DAX.

About Lee Hawthorn

Business Intelligence Consultant
This entry was posted in Power Pivot and tagged , . Bookmark the permalink.

Leave a Reply