The FILTER() function in DAX can be challenging to tame. You can tap into some pitfalls, leading to bad performance of your DAX code. Here are some examples of how to use FILTER() and how not.

Introduction
More than a year ago, I wrote an article about the FILTER() function:
There I explained some details about this powerful function.
What I didn’t do is to explain and show some performance-relevant details of FILTER().
If you are new to this function or are unsure how to use it, jump to that article to get a basic understanding.
There is some redundancy between my old and this article, but it’s never wrong to learn the basics.
In addition, to show you how to use the FILTER() function, I will show you the consequences of each variant on the performance and efficiency of each variant.
For this, I will use DAX Studio and the Server Timings in Dax Studio to get performance statistics.
If you don’t know this feature in DAX Studio or are unsure about the interpretation of the Metrics shown there, read this article, where I dig into this feature:
The base query
To define our starting point, I define a base query on my Demo Dataset (See the References section for Details):
I want to get a list of Countries and the corresponding Sales, but I want to restrict the Brands of the sold Products to these three:
- Contoso
- Northwind Traders
- Fabrikam
The base query is the following:
// Basic Query with the Measure using a direct Filter
DEFINE
MEASURE 'All Measures'[RestrictedRetailSales] =
VAR ListOfBrands = {"Contoso", "Northwind Traders", "Fabrikam" }
VAR Result =
CALCULATE(
[Sum Online Sales]
,'Product'[BrandName] IN ListOfBrands
)
RETURN
Result
EVALUATE
CALCULATETABLE(
ADDCOLUMNS(
SUMMARIZE(Geography, Geography[RegionCountryName])
,"Restricted Online Sales", [RestrictedRetailSales])
)
I will show you how we can alter the line ‘Product'[BrandName] IN ListOfBrands and see the consequences of these changes.
The Result looks like this (Snippet):

We get 35 rows with all known countries and values for China, Germany, and the United States.
The Server Timings of the base query are the following:

The query is executed reasonably quickly with a duration of 0.3 seconds, and the efficiency is good as well, as 91.6 % of the time is spent in the Storage Engine.
OK, let’s make some modifications and look at the consequences.
The first Variance – Introducing FILTER()
Here is the first variance of the query:
// Query with the Measure using a Filter using the FILTER Function on the table
DEFINE
MEASURE 'All Measures'[RestrictedRetailSales] =
VAR ListOfBrands = {"Contoso", "Northwind Traders", "Fabrikam" }
VAR Result =
CALCULATE(
[Sum Online Sales]
,FILTER('Product'
,'Product'[BrandName] IN ListOfBrands)
)
RETURN
Result
EVALUATE
CALCULATETABLE(
ADDCOLUMNS(
SUMMARIZE(Geography, Geography[RegionCountryName])
,"Restricted Online Sales", [RestrictedRetailSales])
)
This time, I added a filter on the Product table to filter it by the three Brands.
Here are the Server Timings:

The Total execution is almost the same, but the time spent in the Storage Engine is more than 100ms longer.
And, instead of three, we have five Storage Engine Queries. Two of these queries use a CallbackDataID call, meaning that the Storage Engine calls the Formula Engine to execute something it cannot do.
In this case, two times the creation of the list of Brands to filter. DAX Studio marks queries containing a CallbackDataID in bold, as this call is unsuitable for performance and efficiency.
Moreover, the first two queries returned over 2’500 rows each. This is called materialization. We should minimize the size of materialization in our DAX expressions.
In this case, 2’517 is the row count of the entire Product table, which means that the Formula Engine gets the whole table and needs to filter it afterward.
Imagine that we have a table with hundreds of thousands of rows. This variant will load the entire table, which takes:
· A lot of memory
· A lot of time
OK, using FILTER() in this way is not good at all.
The second variance – FILTER with ALL()
With the following variance, we add the ALL() function to the FILTER() call.
The ALL() function removes any Filter on the given table or column and returns a table with all values.
// Query with the Measure using a Filter using the FILTER Function on the Product[Brand] column with ALL()
DEFINE
MEASURE 'All Measures'[RestrictedRetailSales] =
VAR ListOfBrands = {"Contoso", "Northwind Traders", "Fabrikam" }
VAR Result =
CALCULATE(
[Sum Online Sales]
,FILTER(ALL('Product'[BrandName])
,'Product'[BrandName] IN ListOfBrands)
)
RETURN
Result
EVALUATE
CALCULATETABLE(
ADDCOLUMNS(
SUMMARIZE(Geography, Geography[RegionCountryName])
,"Restricted Online Sales", [RestrictedRetailSales])
)
The result is still the same, but the Server Timings are radically different from the first variance:

With this variance, we get almost the same queries and timings as the original query without using FILTER().
If we look closely, we discover that the Storage Engine queries are the same in both.
Consequently, both queries are equivalent in terms of performance and efficiency.
This is very interesting as it shows the intelligence of the two Engines.
The differences are the following:
- The base and the second variance replace the filter on the Brand column
-
The first variance adds a Filter to the Brand column without changing the existing filter context. This causes additional queries.
It’s up to you which variance you use. As a lazy person, I prefer to type less code to get the same result.
The third variance – Adding VALUES() instead of ALL()
Let’s replace the ALL() function with the VALUES() function.
The VALUES() function with one column returns a table with the source column’s distinct values (excluding duplicates).
// Query with the Measure using a Filter using the FILTER Function on the Product[Brand] column with VALUES()
DEFINE
MEASURE 'All Measures'[RestrictedRetailSales] =
VAR ListOfBrands = {"Contoso", "Northwind Traders", "Fabrikam" }
VAR Result =
CALCULATE(
[Sum Online Sales]
,FILTER(VALUES('Product'[BrandName])
,'Product'[BrandName] IN ListOfBrands)
)
RETURN
Result
EVALUATE
CALCULATETABLE(
ADDCOLUMNS(
SUMMARIZE(Geography, Geography[RegionCountryName])
,"Restricted Online Sales", [RestrictedRetailSales])
)
The result is still the same: 35 rows with all countries and three values.
These are the Server Timings:

Again, we see the same three SE (Storage Engine) queries (Which look the same as with the base and the second query) and almost the same duration.
The numbers vary by a minimal amount between executions. But they are very similar to the base query.
This shows that using VALUES() is the same as using a simple filter predicate or using FILTER() with ALL().
The fourth variance – Adding one more filter
In addition to filtering the Brand, I want to filter by the product color as well:
// Query with the Measure using a Filter using the FILTER Function on the Product[Brand] column with ALL()
// This time with two columns
DEFINE
MEASURE 'All Measures'[RestrictedRetailSales] =
VAR ListOfBrands = {"Contoso", "Northwind Traders", "Fabrikam" }
VAR Result =
CALCULATE(
[Sum Online Sales]
,FILTER(ALL('Product'[BrandName], 'Product'[ColorName])
,'Product'[BrandName] = "Contoso"
&& 'Product'[ColorName] = "Red"
)
)
RETURN
Result
EVALUATE
CALCULATETABLE(
ADDCOLUMNS(
SUMMARIZE(Geography, Geography[RegionCountryName])
,"Restricted Online Sales", [RestrictedRetailSales])
)
Of course, the numbers in the query result change as we only want to get the Red Products Sales.
Here are the Server Timings:

The result is fascinating.
The query is much faster than before and uses only two SE queries.
As you can see in the image above, we get one query with one WHERE filtering both columns in the Product table.
This means that the SE is very intelligent in combining filters on one table, as it eliminates the first query and executes only two instead of three queries.
The fifth variance – Filtering columns from two tables
The next step is to try to filter two columns from two different tables:
- Brand = "Contoso" in the Product table
- Continent = "Europe" from the Customer table
Here is the starting point for this requirement.
// Query with the Measure using a Filter using the FILTER Function on the Product[Brand] column with VALUES()
// This time with two columns from two different tables
DEFINE
MEASURE 'All Measures'[RestrictedRetailSales] =
VAR ListOfBrands = {"Contoso", "Northwind Traders", "Fabrikam" }
VAR Result =
CALCULATE(
[Sum Online Sales]
,FILTER(ALL('Product'[BrandName])
,'Product'[BrandName] = "Contoso"
&& 'Customer'[ContinentName] = "Europe" - < Why this cannot function? )
)
RETURN
Result
EVALUATE
CALCULATETABLE(
ADDCOLUMNS(
SUMMARIZE(Geography, Geography[RegionCountryName])
,"Restricted Online Sales", [RestrictedRetailSales])
)
This doesn’t work:

The reason why this cannot function is that the second column is from another table that is not part of the Input of the FILTER function:
FILTER(ALL('Product'[BrandName]) <--Input table
,'Product'[BrandName] = "Contoso"
& 'Customer'[ContinentName] = "Europe" <-- Not part of input table
)
Let’s try it in another way:
// Query with the Measure using a direct Filter in CALCULATE, but without FILTER
// Again with two columns from two different tables
DEFINE
MEASURE 'All Measures'[RestrictedRetailSales] =
VAR ListOfBrands = {"Contoso", "Northwind Traders", "Fabrikam" }
VAR Result =
CALCULATE(
[Sum Online Sales]
,'Product'[BrandName] = "Contoso"
,'Customer'[ContinentName] = "Europe" - < This works, But without FILTER()
)
RETURN
Result
EVALUATE
CALCULATETABLE(
ADDCOLUMNS(
SUMMARIZE(Geography, Geography[RegionCountryName])
,"Restricted Online Sales", [RestrictedRetailSales])
)
We use direct filters (predicates) this time without the FILTER() function.
This works, and the execution is very fast and efficient:

But we want to see something else.
How can we use the FILTER function with columns from separate tables?
The sixth variance – Building a table for FILTER()
We must build an input table for FILTER() from different source tables to achieve this.
For this purpose, we use SUMMARIZECOLUMNS() to generate a table combining the values from the given columns:
// Query with the Measure using a Filter using the FILTER Function with two columns from two separate tables
DEFINE
MEASURE 'All Measures'[RestrictedRetailSales] =
VAR ListOfBrands = {"Contoso", "Northwind Traders", "Fabrikam" }
VAR Result =
CALCULATE(
[Sum Online Sales]
,FILTER(SUMMARIZECOLUMNS('Product'[BrandName]
,'Customer'[Continent])
,'Product'[BrandName] = "Contoso"
&& 'Customer'[Continent] = "Europe" - < This works, as we build a table with SUMMARIZECOLUMN() as the input for FILTER()
)
)
RETURN
Result
EVALUATE
CALCULATETABLE(
ADDCOLUMNS(
SUMMARIZE(Geography, Geography[RegionCountryName])
,"Restricted Online Sales", [RestrictedRetailSales])
)
The SE can interpret this variance to execute only one query to get the result (The second query retrieves only the list of the countries):

Moreover, the Total execution time is very short.
But, before we conclude, I have one more variance for you.
The seventh variance – Using CROSSJOIN() for FILTER()
The last variance uses the CROSSJOIN() function.
This function returns a table with the multiplication of each row from the first input table with each row from the second table.
Read the article or watch the video in the DAX Guide article linked above for details.
// Query with the Measure using a Filter using the FILTER Function with two columns from two different tables
DEFINE
MEASURE 'All Measures'[RestrictedRetailSales] =
VAR ListOfBrands = {"Contoso", "Northwind Traders", "Fabrikam" }
VAR Result =
CALCULATE(
[Sum Online Sales]
,FILTER(CROSSJOIN(ALL('Product'[BrandName])
,ALL('Customer'[ContinentName]))
,'Product'[BrandName] = "Contoso"
&& 'Customer'[ContinentName] = "Europe" - < This works, as we build a table with SUMMARIZECOLUMN() as the input for FILTER()
)
)
RETURN
Result
EVALUATE
CALCULATETABLE(
ADDCOLUMNS(
SUMMARIZE(Geography, Geography[RegionCountryName])
,"Restricted Online Sales", [RestrictedRetailSales])
)
The result still doesn’t change. But the execution pattern is very different:

Even though the Total execution time isn’t much longer, now we have four SE Queries.
But the first two are executed extremely fast.
The first Query retrieves the list of all continents (6 rows), and the second retrieves the list of all brands (14 rows).
The third and fourth queries are the same as in the previous variances.
But there are more things to consider with this variance:
- The Cross-Join is done by the Formula Engine (FE). This isn’t a problem with such a small number of values. But when you have hundreds or thousands of values, this will take a tremendous amount of time.
- This variance generates a table with two columns (Brands and Continents) with all possible combinations. This table is filtered with two Predicates (Brand = "Contoso" and Continent = "Europe"). Finally, the generated table is applied as Filter to the CALCULATE() function.
- You must use the ALL() function in order to use the CROSSJOIN() function. Without the ALL function, you will get an error. As an alternative, use the VALUES() function, which leads to the same result.
- This variance requires much writing without any benefit, as the SE queries are the same as in the two variances shown above. But when you need to construct an input table for FILTER() in this specific way, now you know it is possible.

Conclusion
As you have seen, there are a lot of possible variances for using the FILTER function in DAX.
But take care. What appeared to be very quick in my example can be very different in your scenario, as you might have another situation with your data.
The more distinct values you have in the columns you need to filter, the more you need to pay attention to how you use FILTER().
In my opinion, the direct filter approach is the first and most efficient method as it requires the least effort and typing. In most cases, it works right.
So, don’t overwork or overthink your Measures, as the most uncomplicated and straightforward approach might be the correct one.
Read this if you want to learn more about this approach:
References
I use the Contoso sample dataset, like in my previous articles. You can download the ContosoRetailDW Dataset for free from Microsoft here.
The Contoso Data can be freely used under the MIT License, as described here.
I enlarged the dataset to make the DAX engine work harder. The Online Sales table contains 71 million rows (instead of 12.6 million rows), and the Retail Sales table contains 18.5 million rows (instead of 3.4 million rows).
If you appreciate my work, feel free to support me through
Or scan this QR Code:

Any support is greatly appreciated.
Thank you.