Context and Filtering

The single most important concept to understand when writing DAX calculations is context. Context is what separates DAX, as a dynamic analysis language, from Excel functions or SQL queries – or Power Query scripts, for that matter. While all of these only return different results when the data changes (with some exceptions like when using parameters), a single DAX formula can provide many different results depending on where and how you use it: the context.

DAX context is also the key to achieving advanced results with DAX. After you have overcome typical beginner's mistakes, like not knowing what DAX functions to use, incorrect syntax, or forgetting parentheses, issues with context are the most common problem when working with DAX. We will even go as far as to state:

Every problem in DAX comes from context, and every solution is found by closely examining context.

This statement is seldom negated!

In this chapter, we discuss foundational...

The Power BI model

The examples in this chapter are taken from a simple Power BI model. The model consists of one fact table, fSales, with some filter tables:

Figure 1.4.1: The sample Power BI model

The model file for this chapter, 1.4 Context and filtering.pbix, can be downloaded from https://github.com/PacktPublishing/Extreme-DAX/tree/main/Chapter1.4.

Introduction to DAX context

The generic term for DAX context is evaluation context: it is the context in which a DAX formula is evaluated, leading to a specific result. We like to distinguish between three types of context:

• Row context
• Query context
• Filter context

In most Power BI documentation and publications, only two types of context are identified: row and filter context. The term query context has been used in relation to Power Pivot in Excel since way before Power BI came into being (yes, we're that old), and we have kept using it. In our experience from DAX courses, distinguishing between query context and filter context helps people to understand more complex scenarios.

Let's look at each context type in more detail.

Row context

Row context is the type of context you work with when creating calculated columns. A DAX formula defining a calculated column is evaluated for each row in the table. The result of the calculation...

DAX filtering: Using CALCULATE

Changing context using DAX code is one of the most powerful features of DAX. The DAX CALCULATE function, which is used for context transformation, is arguably the most important DAX function. By specifying filter expressions in CALCULATE, you can control the subsets of rows your formula works on. This can be done by adding or replacing filters, but also by removing filters from the context. As relationships play an important role in context by propagating filters, activating or inactivating relationships or changing their filter propagation behavior is a form of context transformation as well.

Let's start with a sample DAX measure:

SalesLargeUnitAmount =
CALCULATE(
    SUM(fSales[SalesAmount]),
    fSales[UnitAmount] > 25
)

This measure returns the sales on transactions in which more than 25 units were sold. The first argument of CALCULATE is the calculation to be performed, in this case, the sum of the SalesAmount column in fSales...

Time intelligence

There are probably hardly any Power BI models that do not include some analysis over time. We want to compare current results with those of last year, for instance. Many other calendar-related insights may also be needed, like year-to-date results, rolling totals, or growth rates from any other past period of time. The difficulty is that the Gregorian calendar is quite messy: most years have 365 days, but some have 366, and months can have anywhere between 28 and 31 days.

These calendar complexities notwithstanding, calendar-based analysis is simply about filtering to change context. Consider the year-to-date sales chart below:

Figure 1.4.9: A year-to-date chart

By definition of year-to-date, what you see in, say, the August column, is the total sales in the period of January 1, 2021 up until August 31, 2021. However, the query context for this column contains a filter on year (2021) and month (August), causing a selection of August 1 to August 31...

Changing relationship behavior

In Chapter 1.2, Model Design, you learned that there can be multiple relationships between tables, but only one of those relationships can be active. The same is true for relationship paths between tables: a Power BI model allows for only one active path between any two tables in the model. This is of course only useful if the inactive relationships can be made active if you need to. You can do this by using the function USERELATIONSHIP.

The function USERELATIONSHIP is used by including it as a filter argument in CALCULATE. This may seem surprising, but it really makes sense. A relationship between a fact table and a filter table ensures that a selection in the filter table propagates to the fact table. Activating another relationship means that that relationship now propagates the selection to the fact table, with the effect that different rows in the fact table are selected. In other words: activating another relationship means changing...

Table functions in DAX

There is a lot you can do with basic aggregation functions like SUM and AVERAGE, in combination with DAX filtering using CALCULATE. But the DAX language goes beyond that. This section is about table functions, which open up an ocean of more advanced calculations in DAX. In Part 2 of this book, you will find that many of the business scenarios discussed involve DAX table functions.

Table aggregations

To start, let's look closely at a simple aggregation in DAX:

Sales1 = SUM(fSales[SalesAmount])

The SUM function in this formula traverses the fSales table and retrieves the value in the SalesAmount column from each row. All these values are summed up to provide the end result.

Because of the special way a Power BI model encodes and stores data (see Chapter 1.2, Model Design), this may not be what happens technically. Logically, however, this is what SUM does, and that is what we are interested in here.

Now, suppose the...

Filtering with table functions

One of the most profound insights when working with DAX is the deep connection between tables and filtering. In this section, you will learn what this connection is, and how to leverage it.

Using CALCULATETABLE

As we discussed earlier in this chapter, table expressions used in table aggregation functions like SUMX are evaluated in the same context as the table aggregation function itself. This is not always what you want: sometimes, you need a different context. DAX provides a function for precisely this purpose: CALCULATETABLE.

Like its cousin CALCULATE, CALCULATETABLE changes context before evaluating an expression. In CALCULATE, this expression must return a scalar value; in CALCULATETABLE, it must be a table expression. Apart from that, the function works with the same four-step process:

1. Set up a filter context.
2. Remove existing filters from columns or tables referred to in the filter arguments.
3. Add new filters as...

DAX variables

DAX table functions and filtering greatly enhance the complexity of calculations that can be done with DAX. The flip side is that formulas can become quite long. More importantly, with all the different contexts in play, it can be problematic to obtain correct results.

DAX variables make life much easier when designing advanced DAX code. The name is somewhat odd, as the purpose of DAX variables is that you can evaluate something once, and use it later in other circumstances (other contexts, typically) without worrying about the evaluation of the variable. In other words, a DAX variable is used as a constant!

A variable is declared using the VAR keyword. Multiple variables can be declared, and the declaration of a variable can use the value of another variable declared earlier. Declarations of variables are closed off by the RETURN keyword:

VAR ThisValue = 5
RETURN
...

It is good to know that DAX variables can be used in any expression in a DAX formula...

Summary

In this chapter, you have learned about row context, query context, and filter context and the role that contexts play in the evaluation of DAX formulas. We have discussed how contexts can be transformed using the CALCULATE function, by removing filters and adding filters to an existing context. In addition, we looked at time intelligence functions, which provide filters specifically tailored to the Gregorian calendar.

We then focused on DAX table functions, which give us the ability to aggregate over tables and the use of custom-made, virtual tables within DAX formulas. Using virtual tables provides a wealth of analytics capabilities on top of what is already possible using "standard" DAX functions and filtering. We talked about the deep connection between tables and filters, which allows for using any table as a filter. And, finally, DAX variables were discussed, which make it easier to implement complex logic in DAX and add to the readability of DAX code as...