Evaluation contexts – part 1

Understanding the concept of the evaluation context in DAX is probably the most important concept you will need to learn, if you are to master the use of DAX. In this section, we will have a brief introduction to the concept and will take a more in-depth look in later chapters.

Evaluation contexts are the basis of advanced DAX functionality. They are used to determine the evaluation of a DAX formula and the corresponding result that's given, which will vary depending on the current context. It is this ability that enables you to perform dynamic analysis, in which the results of a DAX formula can change to reflect the current row or a cell selection, or any filters or slicers that may be applied. Understanding context and using context effectively is essential for building powerful DAX formulas and being able to effectively troubleshoot problems with DAX expressions.

There are two types of evaluation contexts in DAX:

• Row context
• Filter context

You may also see references to a query context in Microsoft documentation, but this is essentially another form of filter context.

Row context

The easiest way to think of row context is as the current row in a table. It applies when you add a calculated column to a table. When you use an expression to define your calculated column, it is executed for every row in the table. For example, if you have a table with a thousand rows in it, the expression will be evaluated one thousand times, once for every row in the table, each with a different row context.

The row context can use values from the same row of the table or rows from related tables:

Figure 1-14: A calculated column being created in Excel Power Pivot

Figure 1-14 shows a calculated column called Sale amount that multiplies the value in the Quantity column by the value in the Unit Price column. Once the data is loaded into the data model from the data source, the calculated column is populated by iterating through each row of the table and calculating the value based on the values contained in the Quantity column and the Unit Price column, for that row. In other words, the value of the calculated column is generated based on the row context as defined by that individual row.

If you have a relationship between tables, the expression used to define a calculated column can also access the columns of a related table by using the RELATED function:

Figure 1-15: The one-to-many relationship between Product and Sales

In Figure 1-15, we can see that there is a one-to-many relationship between the Product table and the Sales table. By creating a calculated column with the following expression, it's possible to add the total weight to the Sales table by multiplying the value of the Quantity column by the value of the Weight column in the related Product table:

=
IF (
    ISBLANK ( RELATED ( 'Product'[Weight] ) ),
    0,
    [Quantity] * RELATED ( 'Product'[Weight] )
)

The following screenshot, Figure 1-16, shows the new total weight column added to the Sales table, with values generated for each row:

Figure 1-16: The total weight column added to the Sales table

In the preceding example, the ISBLANK function has been used in conjunction with the IF function to return a zero when a value is not returned from the related table. This would happen when a product in the Sales table does not exist in the related Product table.

Filter context

The filter context is more complex to understand than the row context, but it can be defined simply as the set of filters that are applied to a data model before the evaluation of a DAX expression begins, which will alter the value returned.

The easiest way to illustrate the filter context is by using a PivotTable:

Figure 1-17: Pivot table showing total sales amount by calendar year for product categories

In Figure 1-17, the PivotTable shows the total sales amount of products by calendar year for each product category. The highlighted cell, showing $310,194.59, has a filter context for the calendar year 2008 and the product category of computers.

The filter context has the following sources of filter:

• Row selection
• Column selection
• Slicer selection
• Filter selection
• A PivotTable filter

Figure 1-18 shows a Power BI report that has slicers for product category and channel. The total sales amount shown in the card visual is the total sales amount with a filter context for the product category of Cell phones and where the sales channel is equal to Catalog:

Figure 1-18: Power BI report with slicers for product category and channel

The filter context will automatically propagate through relationships defined in the data model. In Excel Power Pivot and SQL Analysis Services, only the one-to-many direction is supported, but Power BI has the facility for relationships to be bi-directional.

With a one-to-many relationship, a filter applied to the one side of the relationship automatically filters the rows of the table on the many side of the relationship. If the table on the many side has another table that has a one-to-many relationship with it, the filters do not affect that table, unless you set the relationship to be bi-directional (in Power BI only). We will look at relationships between tables in more detail when we come to looking at data modeling.