Understanding Evaluation Contexts in DAX

Working with many tables

We just started learning contexts, and this led us to some interesting (and surprising) results up to now. You might have noticed that we deliberately used only one table: Product. With only one table, you need to face only interactions between row context and filter context in the same expression.

Very few data models contain just one single table. It is most likely that in your data model you will have many tables linked by relationships. Thus, an interesting question is “How do the two contexts behave regarding relationships?” Moreover, because relationships have a direction, we need to understand what happens on the one and on the many side of a relationship. Finally, to make things a bit harder, please recall that relationships can be unidirectional or bidirectional, depending on how you defined the cross-filter direction on relationship itself.

If you create a row context on a table on the many side of the relationship, do you expect it to let you use columns of the one side? Moreover, if you create a row context on the one side of the relationship, do you expect to be able to access columns from the table on the many side? In addition, what about the filter context? Do you expect to put a filter on the many table and see it propagated to the table on the one side? Any answer could be the correct one, but we are interested in learning how DAX behaves in these situations, that is, understand how the DAX language defines propagation of contexts through relationships. As you are going to learn, there are some subtle interactions between contexts and relationships and learning them requires some patience.

In order to examine the scenario, we use a data model containing six tables, which you can see in Figure 4-13.

There are a couple of things to note about this model:

• There is a chain of one-to-many relationships starting from Sales and reaching Product Category, through Product and Product Subcategory.
• The only bidirectional relationship is the one between Sales and Product. All remaining relationships are set to be one-way cross-filter direction.

Now that we have defined the model, let’s start looking at how the contexts behave by looking at some DAX formulas.

Row contexts and relationships

The interaction of row contexts and relationships is very easy to understand, because there is nothing to understand: they do not interact in any way, at least not automatically.

Imagine you want to create a calculated column in the Sales table containing the difference between the unit price stored in the fact table and the product list price stored in the Product table. You could try this formula:

Sales[UnitPriceVariance] = Sales[UnitPrice] - Product[UnitPrice]

This expression uses two columns from two different tables and DAX evaluates it in a row context that iterates over Sales only, because you defined the calculated column within that table (Sales). Product is on the one side of a relationship with Sales (which is on the many side), so you might expect to be able to gain access to the unit price of the related row (the product sold). Unfortunately, this does not happen. The row context in Sales does not propagate automatically to Product and DAX returns an error if you try to create a calculated column by using the previous formula.

If you want to access columns on the one side of a relationship from the table on the many side of the relationship, as is the case in this example, you must use the RELATED function. RELATED accepts a column name as the parameter and retrieves the value of the column in a corresponding row that is found by following one or more relationships in the many-to-one direction, starting from the current row context.

You can correct the previous formula with the following code:

Sales[UnitPriceVariance] = Sales[UnitPrice] - RELATED ( Product[UnitPrice] )

RELATED works when you have a row context on the table on the many side of a relationship. If the row context is active on the one side of a relationship, then you cannot use it because many rows would potentially be detected by following the relationship. In this case, you need to use RELATEDTABLE, which is the companion of RELATED. You can use RELATEDTABLE on the one side of the relationship and it returns all the rows (of the table on the many side) that are related with the current one. For example, if you want to compute the number of sales of each product, you can use the following formula, defined as a calculated column on Product:

Product[NumberOfSales] = COUNTROWS ( RELATEDTABLE ( Sales ) )

This expression counts the number of rows in the Sales table that correspond to the current product. You can see the result in Figure 4-14.

It is worth noting that both, RELATED and RELATEDTABLE, can traverse a long chain of relationships to gather their result; they are not limited to a single hop. For example, you can create a column with the same code as before but, this time, in the Product Category table:

'Product Category'[NumberOfSales] = COUNTROWS ( RELATEDTABLE ( Sales ) )

The result is the number of sales for the category, which traverses the chain of relationships from Product Category to Product Subcategory, then to Product to finally reach the Sales table.

The only limitation—with regards to chains of relationships—is that all the relationships need to be of the same type (that is, one-to-many or many-to-one), and all of them going in the same direction. If you have two tables related through one-to-many and then many-to-one, with an intermediate bridge table in the middle, then neither RELATED nor RELATEDTABLE will work. A 1:1 relationship behaves at the same time as a one-to-many and as a many-to-one. Thus, you can have a 1:1 relationship in a chain of one-to-many without interrupting the chain.

Let’s make this concept clearer with an example. You might think that Customer is related with Product because there is a one-to-many relationship between Customer and Sales, and then a many-to-one relationship between Sales and Product. Thus, a chain of relationships links the two tables. Nevertheless, the two relationships are not in the same direction.

We call this scenario a many-to-many relationship. In other words, a customer is related to many products (the ones bought) and a product is related to many customers (the ones who bought the product). You will learn the details of how to make many-to-many relationships work later; let’s focus on row context, for the moment. If you try to apply RELATEDTABLE through a many-to-many relationship, the result could be not what you might expect. For example, consider a calculated column in Product with this formula:

Product[NumOfBuyingCustomers] = COUNTROWS ( RELATEDTABLE ( Customer ) )

You might expect to see, for each row, the number of customers who bought that product. Unexpectedly, the result will always be 18869, that is, the total number of customers in the database, as you can see in Figure 4-15.

RELATEDTABLE cannot follow the chain of relationships because they are not in the same direction: one is one-to-many, the other one is many-to-one. Thus, the filter from Product cannot reach Customers. It is worth noting that if you try the formula in the opposite direction, that is, you count, for each of the customers, the number of bought products, the result will be correct: a different number for each row representing the number of products bought by the customer. The reason for this behavior is not the propagation of a filter context but, rather, the context transition created by a hidden CALCULATE inside RELATEDTABLE. We added this final note for the sake of completeness. It is not yet time to elaborate on this: You will have a better understanding of this after reading Chapter 5, “Understanding CALCULATE and CALCULATETABLE.”

Filter context and relationships

You have learned that row context does not interact with relationships and that, if you want to traverse relationships, you have two different functions to use, depending on which side of the relationship you are on while accessing the target table.

Filter contexts behave in a different way: They interact with relationships in an automatic way and they have different behaviors depending on how you set the filtering of the relationship. The general rule is that the filter context propagates through a relationship if the filtering direction set on the relationship itself makes propagation feasible.

This behavior is very easy to understand by using a simple pivot table with a few measures. In Figure 4-16 you can see a pivot table browsing the data model we have used so far, with three very simple measures defined as follows:

[NumOfSales]     := COUNTROWS ( Sales )
[NumOfProducts]  := COUNTROWS ( Product )
[NumOfCustomers] := COUNTROWS ( Customer )

The filter is on the product color. Product is the source of a one-to-many relationship with Sales, so the filter context propagates from Product to Sales, and you can see this because the NumOfSales measure counts only the sales of products with the specific color. NumOfProducts shows the number of products of each color, and a different value for each row (color) is what you would expect, because the filter is on the same table where we are counting.

On the other hand, NumOfCustomers, which counts the number of customers, always shows the same value, that is, the total number of customers. This is because the relationship between Customer and Sales, as you can see Figure 4-17, has an arrow in the direction of Sales.

The filter started from Product, then propagated to Sales (following the arrow from Product to Sales, which is enabled) but then, when it tried to propagate to Customer, it did not find the arrow letting it continue. Thus, it stopped. One-way relationships permit propagation of the filter context in a single direction, not in both.

You can think that the arrows on the relationships are like semaphores. If they are enabled, then the semaphore light is green and the propagation happens. If, on the other hand, the arrow is not enabled, then the semaphore light is red and the filter cannot be propagated.

The arrow is always enabled from the one side to the many side of any relationship. You have the option of enabling it from the many side to the one side as well. If you let the arrow disable, then the propagation will not happen from the many to the one side.

You can better appreciate the behavior if you look at the pivot table shown in Figure 4-18. Instead of using the product color on the rows, this time we slice by customer education.

This time the filter starts from Customer. It can reach the Sales table, because the arrow is enabled in the corresponding relationship. Then, from Sales, it can further propagate to Product because the relationship between Sales and Product is bidirectional.

Now you add to the model a similar measure that counts the number of subcategories, such as the following one:

NumOfSubcategories := COUNTROWS ( 'Product Subcategory' )

Adding it to the report, you will see that the number of subcategories is not filtered by the customer education, as shown in Figure 4-19.

This is because the relationship between Product and Product Subcategory is unidirectional, that is it lets the filter propagate in a single direction. As soon as you enable the arrow starting from Product and going to Product Subcategory, you will see that the filter propagates, as you can see in Figure 4-20.

As it happened with the row context, it is not important how many steps you need to traverse to reach a table: as long as there is a chain of enabled relationships, automatic propagation happens. For example, if you put a filter on Product Category, the filter propagates to Product Subcategory, then to Product, and finally to Sales.

It is important to note that there are no functions available to access columns or values from tables following the chain of filter contexts, because propagation of the filter context in a DAX expression happens automatically, whereas propagation of row contexts does not and it is required to specify the propagation using RELATED and RELATEDTABLE.

Introducing VALUES

The previous example is very interesting, because it shows how to compute the number of customers who bought a product by using the direction of filtering. Nevertheless, if you are interested only in counting the number of customers, then there is an interesting alternative that we take as an opportunity to introduce as another powerful function: VALUES.

VALUES is a table function that returns a table of one column only, containing all the values of a column currently visible in the filter context. There are many advanced uses of VALUES, which we will introduce later. As of now, it is helpful to start using VALUES just to be acquainted with its behavior.

In the previous pivot table, you can modify the definition of NumOfCustomers with the following DAX expression:

[NumOfCustomers] := COUNTROWS ( VALUES ( Sales[CustomerKey] ) )

This expression does not count the number of customers in the Customer table. Instead, it counts the number of values visible in the current filter context for the CustomerKey column in Sales. Thus, the expression does not depend on the relationship between Sales and Customers; it uses only the Sales table.

When you put a filter on Products, it also always filters Sales, because of the propagation of the filter from Product to Sales. Therefore, not all the values of CustomerKey will be visible, but only the ones present in rows corresponding to sales of the filtered products.

The meaning of the expression is “count the number of customer keys for the sales related to the selected products.” Because a customer key represents a customer, the expression effectively counts the number of customers who bought those products.

Using VALUES instead of capitalizing on the direction of relationships comes with both advantages and disadvantages. Certainly setting the filtering in the model is much more flexible, because it uses relationships. Thus, you can count not only the customers using the CustomerKey, but also any other attribute of a customer (number of customer categories, for example). With that said, there might be reasons that force you to use one-way filtering or you might need to use VALUES for performance reasons. We will discuss these topics in much more detail in Chapter 12, “Advanced relationship handling.”

Introducing ISFILTERED, ISCROSSFILTERED

Two functions are very useful and might help you gain a better understanding of the propagation of filter contexts. Moreover, learning them is a good way to introduce one of the most interesting concepts of pivot table computation, that is, detection of the cell for which you are computing a value from inside DAX.

These two functions aim to let you detect whether all the values of a column are visible in the current filter context or not; they are:

• ISFILTERED: returns TRUE or FALSE, depending on whether the column passed as an argument has a direct filter on it, that is, it has been put on rows, columns, on a slicer or filter and the filtering is happening for the current cell.
• ISCROSSFILTERED returns TRUE or FALSE depending on whether the column has a filter because of automatic propagation of another filter and not because of a direct filter on it.

In this section, we are interested in using the functions to understand the propagation of filter contexts. Thus, we are going to create dummy expressions, which are only useful as learning tools.

If you create a new measure with this definition:

[CategoryFilter] := ISFILTERED ( 'Product Category'[Category] )

This simple measure returns the value of the ISFILTERED function applied to the product category name. You can then create a second measure that makes the same test with the product color. So the code will be:

[ColorFilter] := ISFILTERED ( Product[ColorName] )

If you add both measures to a pivot table, placing the categories in a slicer and the colors on the rows, the result will be similar to Figure 4-21.

The interesting part is that the category is never filtered because, even if we added a slicer, we did not make a selection on it. The color, on the other hand, is always filtered on rows, because each row has a specific color, but not in the grand total, because the filter context there does not include any selection of products.

If you now select some values from the Category slicer, the result changes. Now the category always has a filter, as you can see in Figure 4-22. In fact, the filter context introduced by the slicer is effective even at the grand total level of the pivot table.

ISFILTERED is useful to detect when a direct filter is working on a column. There are situations where a column does not show all of its values, not because you are filtering the column, but because you placed a filter on another column. For example, if you filter the color and ask for the VALUES of the product brand, you will get as a result only the brands of products of that specific color. When a column is filtered because of a filter on another column, we say that the column is cross-filtered and the ISCROSSFILTERED function detects this scenario.

If you add these two new measures to the data model that check, this time, the ISCROSSFILTERED of color and category:

[CrossCategory] := ISCROSSFILTERED ( 'Product Category'[Category] )
[CrossColor] := ISCROSSFILTERED ( Product[Color] )

Then you will see the result shown in Figure 4-23.

You can see that color is cross-filtered and category is not. An interesting question, at this point, is “Why is the category not filtered?” When you filter a color, you might expect to see only the categories of product of that specific color. To answer the question you need to remember that the category is not a column of the Product table. Instead, it is part of Product Category and the arrows on the relationship do not let the relationship propagate. If you change the data model, enabling bidirectional filtering on the full chain of relationships from Product to Product Category, then the result will be different, as is visible in Figure 4-24.

In this section, you have seen some examples of ISFILTERED and ISCROSSFILTERED, mainly for educational purposes, because you used them only to get a better understanding of how a filter context propagates through relationships. In following chapters, by writing advanced DAX code, you will learn why these two functions are so useful.