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 ﬁlter context and a row context, which are automatically deﬁned 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 diﬀerent types of context have diﬀerent behaviours when relationships between tables are involved. The key concept is that the row context does not propagate over relationships, whereas the ﬁlter context propagates through one-to-many relationships, but not in the opposite direction. It is critically important to understand side eﬀects 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 ﬁlter context for a speciﬁed column. This can be useful to count the number of distinct values that are active in the current ﬁlter context for a particular column, such as a customer key, resulting in the number of distinct customers who bought something on speciﬁed dates, products, and so on. The ﬁlter context has a diﬀerent 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 ﬁlter 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 diﬀerent 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 ﬁlter 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.
Access all content, including full-length training videos, demo files, and articles, right now! Payment is instant, and you will receive a tax invoice, straight away.
Our content is updated every month—get the best value by buying a 1 year membership.
Your satisfaction is paramount: we offer a no-quibble refund guarantee.