Table of Contents
  • 2026-01-12
  • Daniel Otykier

Code action DI005 (Improvement) Rewrite table filter as scalar predicate

Description

Rewrite CALCULATE filter arguments as scalar predicates when possible, instead of using the FILTER function.

Example 1

Change:

CALCULATE([Total Sales], FILTER(Products, Products[Color] = "Red"))

To:

CALCULATE([Total Sales], KEEPFILTERS(Products[Color] = "Red"))

Example 2

Change:

CALCULATE([Total Sales], FILTER(ALL(Products), Products[Color] = "Red"))

To:

CALCULATE([Total Sales], ALL(Products), Products[Color] = "Red")

Example 3

Change:

CALCULATE(
    [Total Sales],
    FILTER(
        ALL(Products), 
        Products[Color] = "Red" 
            && Products[Class] = "High-end"
    )
)

To:

CALCULATE(
    [Total Sales], 
    ALL(Products),
    Products[Color] = "Red", 
    Products[Class] = "High-end"
)

Why is Tabular Editor suggesting this?

Filtering a table inside a CALCULATE filter argument is less efficient than filtering one or more columns from that table. By rewriting the filter as a scalar predicate, you make your code more efficient, consuming less memory and CPU resources.

For example, an expression such as FILTER(Sales, < condition >) will iterate over all rows in the Sales table, evaluating the condition for each row. In contrast, an expression such as Sales[Quantity] > 0 will only iterate over the Quantity column, which is much more efficient, and does not cause all the columns from the Sales table to be added to the filter context.

By using scalar predicates, you also make your code more concise and easier to read.

Behind the scenes, scalar predicates are syntax sugar for a table expression that also uses the FILTER function. However, the FILTER function is applied to a single column, which is more efficient than filtering the entire table.

When this suggestion is not shown

Starting with Tabular Editor 3.25.0, this code action is not suggested when table expansion occurs in the FILTER expression. This is because rewriting a table filter into a scalar predicate can change the semantics of the filter and produce different results in such cases.

Table expansion scenario

Table expansion happens when DAX automatically includes related tables on the "one" side of a relationship. For example, if you filter the Sales table, DAX automatically expands the filter to include related dimension tables like Product, Customer, etc.

When you write:

CALCULATE(
    [Total Sales],
    FILTER(Sales, Sales[Quantity] > 10)
)

The filter operates on the expanded Sales table, which includes all related dimension tables. This means the filter context includes not only the Sales table but also any tables on the "one" side of relationships from Sales.

Why rewriting can change results

Converting a table filter to a scalar predicate changes which tables are affected:

Original (with table expansion):

CALCULATE([Total Sales], FILTER(Sales, Sales[Quantity] > 10))

This filters the expanded Sales table, affecting Sales and all related dimension tables.

After rewrite (scalar predicate):

CALCULATE([Total Sales], Sales[Quantity] > 10)

This filters only the Sales[Quantity] column, which does not trigger table expansion to related dimension tables.

In most cases, this difference doesn't matter because the related dimension tables don't affect the calculation. However, in certain scenarios, particularly when relationships or calculated tables are involved, the two expressions can produce different results. To ensure correctness, Tabular Editor avoids suggesting this rewrite when table expansion is detected.

Further reading