KB 102500
A FILTER function is used instead of modifying the filter context
The FILTER function is used instead of modifying the filter context.
Remarks
The FILTER function can be expensive from a materialization standpoint. When the filter condition only references physical columns of the iterated table, then it is possible to filter the table through the filter context by using CALCULATETABLE.
Example 1
Move the predicate in a CALCULATETABLE filter. Remove the FILTER and use CALCULATETABLE to filter the table moving the condition into a filter argument; use KEEPFILTERS to preserve the same semantics.
Original code
FILTER ( SUMMARIZE ( Sales, Sales[Quantity], Sales[Net Price] ), Sales[Quantity] > 1 )
Possible optimization
CALCULATETABLE ( SUMMARIZE ( Sales, Sales[Quantity], Sales[Net Price] ), KEEPFILTERS ( Sales[Quantity] > 1 ) )
Example 2
Move the predicate in a CALCULATETABLE filter. Remove the FILTER and use CALCULATETABLE to filter the table moving the condition into a filter argument; use KEEPFILTERS to preserve the same semantics.
Original code
FILTER ( ADDCOLUMNS ( SUMMARIZE ( Sales, Sales[Quantity], Sales[Net Price] ), "@Discount", Sales[Net Price] * 0.2 ), Sales[Quantity] > 1 )
Possible optimization
CALCULATETABLE ( ADDCOLUMNS ( SUMMARIZE ( Sales, Sales[Quantity], Sales[Net Price] ), "@Discount", Sales[Net Price] * 0.2 ), KEEPFILTERS ( Sales[Quantity] > 1 ) )