Knowledge Base
Open App
KB 100507

Filtered table as filter argument (multiple columns predicate with AND and OR conditions)


A FILTER function filtering entire table using AND and OR conditions on multiple columns is used as a filter argument in functions such as CALCULATE, CALCULATETABLE, etc.

Remarks

Because the conditions are applied with an || operator, the multiple columns predicate must be kept in a single filter argument, without filtering an entire table.

In order to keep the same semantics as the original filter, apply REMOVEFILTERS on the previously iterated table because the iterator was over ALL of that table.

Recommended articles:

Example

Remove the Customer iterator and use KEEPFILTERS around the predicates to maintain the same semantics, so the filter is applied only to the Customer[Continent], Customer[City], and Customer[Age] columns instead of filtering the entire Customer table.

Original code

CALCULATE (
    [Sales Amount],
    FILTER (
        Customer,
        Customer[Continent] = "North America" 
            && ( Customer[Age] >= 21 || Customer[City] = "Brighton" )
    )
)

Possible optimization

CALCULATE (
    [Sales Amount],
    KEEPFILTERS ( Customer[Continent] = "North America" ),
    KEEPFILTERS ( Customer[Age] >= 21 || Customer[City] = "Brighton" )
)