Learning evaluation contexts in Data Analysis Expression (DAX) is fundamental to understanding the behaviour of this analytical language, and to being able to write correctly functioning formulas for your desired calculations. Marco Russo, a well-known book author from sqlbi.com, teaches you these important concepts in this 50-minute video.

The Evaluation Context consists of a filter context and a row context, which are automatically defined in any query, for example by a pivot table in Excel. The Filter Context is a set of active rows in all the tables of the data model that are considered while evaluating a DAX expression. The Row Context is similar to the notion of a current row, during the iteration over the rows of a table.

These different types of context have different behaviours when relationships between tables are involved. The key concept is that the row context does not propagate over relationships, whereas the filter context propagates through one-to-many relationships, but not in the opposite direction. It is critically important to understand side effects of context on related tables of a selection, and of the manipulation of evaluation contexts, in order to be able to get the desired results from a DAX formula.

Propagation of row context through relationships is possible by using RELATED and RELATEDTABLE functions, whereas the row context has an automatic propagation through relationships, and the VALUES function can be used to obtain the distinct values active in a filter context for a specified column. This can be useful to count the number of distinct values that are active in the current filter context for a particular column, such as a customer key, resulting in the number of distinct customers who bought something on specified dates, products, and so on. The filter context has a different behaviour than the row context, with regards to relationships, and this is explained in detail in the video and, in particular, in an extensive demo.

The manipulation of filter context at the table level can be accomplished by using the FILTER and ALL functions. You can interact with row context by using iteration functions, such as SUMX, and by means of the EARLIER function, which makes it possible to access outer context, when nested row contexts are involved. Frequently you may need to compare the same column reference (such as DimProduct[ListPrice]) by accessing two different row contexts, within the same table, in the same logical condition, for example when calculating custom product rankings. This sounds more complex than it is, but our detailed demo should make it easy.

By watching this video, which includes 8 full-length, high-resolution demos, you will be able to start manipulating row context and filter context. This video will also prepare you for learning about CALCULATE, which is the most important function in DAX. That, and other aspects of this language, are fully explained in the remaining modules of this online course.

Log in or purchase access below to the premium version of this content.