Today’s post is about different ways to set a simple filter in DAX. Although there are plenty of writings covering these patterns, I still see DAX authors get confused on a regular basis. In this post, I am going to clarify the differences among the patterns. The examples are based on a pbix file created from the Adventure Works database that you can download from here. It helps if you have learned about filter context before but you should still follow the general idea outlined here without those knowledge.
Setting a basic filter is one of the first things people do when writing a DAX measure. Let’s say you want to write a measure to calculate the total sales on red products. The two common DAX patterns I saw people use are:
Pattern 1. Calculate(Sum([Sales Amount]), ‘Product'[Color] = “Red”)
Pattern 2. Calculate(Sum([Sales Amount]), Filter(‘Product’, [Color] = “Red”))
Both approaches work as expected in simple models and reports, but from time to time people run into troubles on such simple DAX measures and have difficulty figuring out the underlying reason.
Although Patterns 1 and 2 look very similar, they actually have different semantics in DAX. The differences become obvious when you add the [Color] column and both measures to a flat table chart as shown in Figure 1. While Pattern 1 returns sales of red products in all rows, Pattern 2 returns blank values in all rows except for the red color row and the grand total row.
Under the hood, Pattern 1 is simply a syntax shortcut for the following DAX expression:
Pattern 1.1. Calculate(Sum([Sales Amount]), Filter(All(‘Product'[Color]), [Color] = “Red”))
The key difference is the implied All function in Pattern 1. Because of the All function, Pattern 1 does nothing more than replacing any prior filter on the [Color] column by the new filter [Color] = “Red”, on the other hand, the lack of All function in Pattern 2 means the ‘Product’ table is first filtered by any existing filters from the filter context and then the remaining rows are further filtered by the predicate [Color] = “Red”. This explains why you see those blank values in Figure 1: Since the ‘Product’ table is first filtered down to the rows matching the color of the current row in the flat table and then further filtered by the predicate [Color] = “Red”, the net result is empty if a row has a color other than red; the grand total row has no filter coming from the flat table therefore only the predicate takes effect.
In addition to the unwanted results in Figure 1, Pattern 2 also suffers from an unintended performance hit even when it delivers correct results. For example, if a report has a page filter on ‘Date'[Calendar Year] = 2008, the ‘Product’ table will be filtered down to all products sold in 2008 before the predicate [Color] = “Red” is applied, that means DAX engine needs to scan the ‘Internet Sales’ fact table just to calculate the Filter function itself. Few people would want to scan a fact table only to set a filter on a dimension table.
The All function allows a column name or a table name as its argument. So the following pattern also achieves the same effect as Pattern 1 does.
Pattern 3. Calculate(Sum([Sales Amount]), Filter(All(‘Product’), [Color] = “Red”))
The difference between Pattern 3 and Pattern 1 is that Pattern 3 replaces filters on all columns from the ‘Product’ table in the filter context but Pattern 1 only replaces prior filter on the [Color] column. As a result, if you have several columns from the ‘Product’ table in the report, all of them will be removed from the filter context, the only remaining filter on the ‘Product’ table is [Color] = “Red”, as shown in Figure 2. As you can see in the picture, the values of measure [m – Sum Filter Pattern 3] is not affected by the filter [Class] = “L” from the flat table.
Today we have discussed the differences among three ways of setting a simple filter in DAX. Although all three patterns may work in most cases and any one of them may be the right solution for a particular report, if you are not sure which one to use, I would start with Pattern 1, or its equivalent Pattern 1.1, due to its minimal side effect.