Table filters vs column filters in Power BI and the concept of expanded tables

September 2, 2023 Guilherme Joaquim

I want to talk about a topic that I didn't quite understand when I started my DAX learning journey.

In this post we are going to talk about:

Short description of the CALCULATE function
The concept of expanded tables
Performance in Power BI when using table filters within CALCULATE
Examples where table filters can be useful

When using the CALCULATE function, should I use column filters, table filters or just omit the FILTER function?

We are going to analyze some examples using a modified version of the Contoso database for educational purposes (Sales fact table with around 2 million rows). Can you explain why the following DAX codes are in fact really different but at the same time deliver the same result in the respective filter context?

Note: both DAX codes provide the same result in this case, but it does not mean that they are the same.

Ok, I know what you might be thinking. Why should I know this? I just want to calculate the SUM of sales with a filter condition. Well, I still remember when I was in university and my professor said to us (I don’t remember the exact words): everyone can learn how to use a software but it is our job as engineers to know what is going on under the hood to analyze the results in order to make a decision. This also applies for DAX. Let’s go back to our topic, ready to dive in?

Let’s first begin with a short introduction of the CALCULATE function, because it will be important to understand our case here.

The CALCULATE function in DAX

The CALCULATE function is the most important function in the DAX language. But as we know: “with great power comes great responsibility”, well in this case I would replace responsibility with “complexity”. It is beyond the scope of this article to explain the CALCULATE function in details. Please refer to SQLBI or to the book “The Definitive Guide to DAX” second edition by Marco Russo and Alberto Ferrari if you want to learn more about it.

Most people writing DAX code with the CALCULATE function use the syntax sugar version (a shortcut). Look at both DAX codes below where it computes the sum of sales where product category equals to audio:

It is important to understand that a Boolean condition in the CALCULATE argument is actually read as a table, a list of values. This matters in order to understand the semantics of CALCULATE and also later on to understand the concept of expanded tables.

Expanded tables (introduction)

Expanded tables is an advanced topic in DAX, but even beginners should be aware of this concept in order to avoid getting unexpected results and/or performance problems (which we will see later here: stay tuned!). Let’s go back to our example, see below the data model used in our example:

Note: the bridge table Brand is there in order to handle different granularitities between Sales and TargetSales fact tables. For more details about this topic refer to my previous post How to handle different granularities in Power BI.

What actually happens if I use a table filter as follows?

According to the concept of expanded tables, when I use the table Sales as a filter argument, I am not only applying the filter to the Sales tables, but also to its expanded version table(s).

Wait, what is a expanded version table in the first place?

Note: here is a demonstration how filter context propagates within a DAX code. Please, do not mistake this filter context propagation with the Star Schema propagation (which is normally one-to-many)

To find the expanded version of a table, you have to go towards the one-side of the relationship (following the many-to-one relationship).

Normally, the expanded versions of a fact table in a Star Schema model will have all the tables in the data model. In this case, the expanded version of Sales includes:

Date table, Product table and Bridge table

Performance in Power BI

You might be asking yourself, well if the result from both DAX codes (table filters and column filters) in this case is the same, should I care about it? YES and YES! And you will see here the performance impact.

Here is the summary of the performance from Power BI using the Performance analyzer from Power BI Desktop (the sales fact table has 2,24 million rows).

Table filters: the matrix took 6,4 seconds to refresh 😱
Column filters: the matrix took 0,3 seconds to refresh

That is already a good indication that table filters can affect the performance of your report (especially when the table is your fact table). But let’s dig deeper! Let’s copy the DAX query from the Performance analyzer and paste the query in DAX Studio. In DAX Studio turn on the Query plan and Server timings. See the results below:

Key takeaways from the results above:

With the use of table filters: we can see from DAX Studio that the query spends much more time in the Formula Engine (61,9%) than in the Storage Engine (38,1%)
With the use of column filters: here the query spends much more time in the Storage Engine (88,3%)

For optimization purposes, we aim to spend more time in the Storage Engine than in the Formula Engine during the query.

Another way to solve it without using CALCULATE

This is a little bit out of the scope of this article, but I like to try different ways to solve a problem and like almost everything in Power BI, there are many ways to solve a problem.

There is a quote from Greg Deckler: “DAX is easy, CALCULATE makes DAX hard”. See the DAX code below without using CALCULATE:

It is worth noting that the performance was really good. It took around 0,2 seconds to refresh the matrix using the Performance analyzer.

Using table filters to solve real problems

No, table filters are not bad! They can be very useful, if used correctly and you understand the concept of expanded tables. We are going to go through 2 examples, where table filters can be used.

Example 1: Calculate the % of sales

A very common business requirement is to calculate the percentage of sales per category. See below the DAX code and the result:

In this case we used the ALL function as a calculate modifier to remove all filters from Sales table. But remember that the Sales table is our fact table. Therefore, doing that we are not only removing the filter from Sales table, but from all its expanded version tables! Here comes in the concept of expanded tables again.

Example 2: Handle different granularities

I already wrote an article about how to handle different granularities in Power BI. There are many ways to handle different granularities in Power BI and using expanded tables is one of them that I didn’t mention in the previous article. The idea of using expanded tables to solve this problem came from Wojciech Pabich. Thank you for that.

Note: my favorite method to handle different granularities in Power BI is using a bridge table setting the cross filter direction to both (for more details refer to my previous article).

Well, our goal here is to select the Brand column from the Product table and filter the TargetSales. Of course, there are other ways to solve this but one possible way is using DAX. See below the DAX code and how the filter propagation works to reach the TargetSales table:

How does it work? The expanded version of the Product table contains: Product and Bridge Table Brand. The filter propagates from Product to Bridge Table Brand (because of the expanded table concept) and then from Bridge Table Brand to TargetSales because of the one-to-many relationship.

Conclusion

In this post we talked about the difference between column and table filters, the concept of expanded tables, the performance impact of using table filters and 2 examples where table filters can be applied to solve real problems.

Marco Russo and Alberto Ferrari wrote in their book “The Definitive Guide to DAX” second edition to try to avoid using table filters whenever possible (I would say especially for beginners), because working with table filters requires much more attention (expanded tables). However, it does not mean that table filters are bad, not at all! They can be very useful and powerful if used correctly and you understand what you are doing.

DATA TRAVELING