Understanding the difference between row context and filter context is important to use DAX correctly. This article introduces you to these two concepts.
DAX expressions operate on columns, you usually write expressions like:
Gross Profit := SUMX ( Sales, Sales[Amount] – Sales[TotalCost] )
In the previous expression, Sales[Amount] and Sales[TotalCost] are column references. A column reference intuitively means that you want to retrieve the value of a column, in the example you obtain the value of Sales[Amount] for the currently iterated row by SUMX.
At the same time, you use a column reference to instruct some functions to perform an action on a column, like in the following measure that counts the number of product names:
NumOfAllProducts := COUNTROWS ( VALUES ( Product[ProductName] ) )
In this case, Product[ProductName] does not retrieve the value of ProductName for a specific row. Instead, you use the column reference to tell VALUES which column to use. In other words, you reference the column, not its value.
A column reference is a somewhat ambiguous definition because you reference the value of a column in a specific row and the full column itself, both with the same syntax. Nevertheless, DAX expressions are typically easy to read because, although ambiguous, the syntax leads to very intuitive expressions.
When you use a column reference to retrieve the value of a column in a given row, you need a way to tell DAX the row to use, out of the table, to compute the value. In other words, you need a way to define the current row of a table. This concept of “current row” the Row Context.
You have a row context whenever you iterate a table, either explicitly (using an iterator) or implicitly (in a calculated column):
- When you write an expression in a calculated column, the expression is evaluated for each row of the table, creating a row context for each row.
- When you use an iterator like FILTER, SUMX, AVERAGEX, ADDCOLUMNS, or any one of the DAX functions that iterate over a table expression.
If a row context is not available, evaluating a column reference produces an error. If you write only a column reference in a DAX measure, you get an error because a row context does not exists. For example, this measure is not valid:
SalesAmount := Sales[Amount]
In order to make it working, you need to aggregate the column, not to refer to its value. In fact, the correct definition of SalesAmount is:
SalesAmount := SUM ( Sales[Amount] )
Every iterator introduces a new row context, and iterators can be nested. For example, you can write:
AverageDiscountedSalesPerCustomer := AVERAGEX ( Customer, SUMX ( RELATEDTABLE ( Sales ), Sales[SalesAmount] * Customer[DiscountPct] ) )
In the innermost expression, you reference both Sales[SalesAmount] and Customer[DiscountPct], i.e. two columns coming from different tables. You can safely do this because there are two row contexts: the first one introduced by AVERAGEX over Customer and the second one introduced by SUMX over Sales. Moreover, it is worth to note that the row context is also used by RELATEDTABLE to determine the set of rows to return. In fact, RELATEDTABLE ( Sales ) returns the sales of the current customer where, by “current”, we mean the currently iterated one by AVERAGEX or, in a more clear definition, the current customer in the row context introduced by AVERAGEX over Customer.
The filter context is the set of filters applied to the data model before the evaluation of a DAX expression starts. When you use a measure in a pivot table, for example, it produces different results for each cell because the same expression is evaluated over a different subset of the data. The Microsoft documentation describes as query context those filters applied by the user interface of a pivot table and, as filter context, those other filters applied by DAX expressions that you can write in a measure. In reality, these filters are almost identical in their effects (the real differences are not important for this introductive article), so we simply defines as filter context the set of filters applied to the evaluation of a DAX expression, usually a measure, regardless of how they have been generated.
For example, the cell highlighted in the following picture has a filter context for year 2007, color equal to Black, and product brand equal to Contoso. This is the reason why its value is different, for example, from the one showing the same year for Fabrikam brand.
You can obtain the same effect by applying a filter with CALCULATE or CALCULATETABLE. For example, the following DAX query returns the same value of the highlighted cell in the previous picture.
EVALUATE ROW ( "Sales Amount", CALCULATE ( [Sales Amount], 'Date'[Calendar Year] = "CY 2007", Product[Color] = "Black", Product[Brand] = "Contoso" ) )
Usually, every cell of a report has a different filter context, which can be defined implicitly by the user interface (such as the pivot table in Excel), or explicitly by some DAX expression using CALCULATE or CALCULATETABLE.
Any filter applied to pivot tables in Excel, or to any user interface element of Power BI Desktop or Power View, always affects the filter context, and never affects the row context in a direct way.
A filter context is a set of filters over the rows of the data model. There is always a filter context for any DAX expression. If the filter context is empty, a DAX expression can iterate all the rows of the tables in a data model. When a filter context is not empty, it limits the rows that a DAX expression can iterate in a data model.
Propagation of Filters
A row context does not propagate through relationships. If you have a row context in a table, you can iterate the rows of a table on the many side of a relationship using RELATEDTABLE, and you can access the row of a parent table using RELATED.
A filter applied on a column of a table affects all the rows of that table, filtering rows that satisfy that filter. If two or more filters are applied to columns of the same table, they are considered in a logical AND condition and only the rows satisfying all the filters are processed by a DAX expression in that filter context.
A filter context automatically propagates through relationships, according to the cross filter direction of the relationship.
In Power Pivot for Excel, such a direction is only one-to-many, so a filter applied on the one side of a relationship affects the rows of the table on the many side of the same relationship, but the filter does not propagate in the opposite direction. For example, consider a model where you have two tables, Product and Customer, each one with a one-to-many relationship to the Sales table. With a single direction of cross filter, if you filter a product, you also filter the sales of a product, but you do not filter the customer who bought those products.
In Power BI Desktop and SQL Server Analysis Services Tabular 2016, you can enable bidirectional cross filtering. By enabling the bidirectional cross filter, once you filter one table, you also filter all the tables on the one side of a relationship. For example, when you filter rows on Product, you implicitly filter Sales, and Customer, filtering in this way those customers who bought the products you selected.
When you write DAX expressions, you can control both the row context and the filter context. Remember that the row context does not propagate automatically through relationships, whereas the filter context traverse relationships independently from the DAX code. However, you can control the filter context and its propagation using DAX functions such as CALCULATE, CALCULATETABLE, ALL, VALUES, FILTER, USERELATIONSHIP, and CROSSFILTER.
This article is a small example of the complete DAX description that you can read in our new book, The Definitive Guide to DAX.