Why does Calculate with Filter return this result?

Question

Suppose I have 2 tables linked on CustID

Customers

CustID CustName    
1       A   
2       B   
3       C

Sales

CustID  SaleAmt

1       20
2       30
3       40
1       50

Visual

CustID  SumMeasure
1       
2       30
3

SumMeasure = Calculate(Sum(Sales[SaleAmt]), Filter(Customers, CustID = 2))

My question is why does the measure return blank in the rows where CustID isn't 2? I originally thought calculate would overwrite an existing filter, meaning that each row would get 30, but that clearly isn't the case when Filter is involved.

The only process I could figure out was based on an article from the SQLBI guys where they state:

ColorGreen = CALCULATE( COUNTROWS(‘Demo’), FILTER( ‘Demo’, ‘Demo'[Color] = “Green” ) )

"We pass the whole Demo table to the FILTER condition, which results a filter of the current context with all the columns! In this way we apply a restrictions on the color green and we get the same result as before (no rows for all the colors but green, the selection of color of the PivotTable is still applied) but, remember, the FILTER is returning ALL the rows."

Does that mean the process is this in the first row:

The implicit filter context(CustID = 1) is applied to Customers(as a sort of pre-filter)
This pre-filtered table is passed to the Filter function where it iterates through row by row doing its comparison
Since there is no row in the Customers table(after the pre-filter) where CustID = 2, it returns a blank table
Filter passes the final filtered table(blank) to Sum
Sum returns a blank result since nothing to sum

If this is not the case, can someone please do a step-by-step of how it's being evaluated? Please note that I'm not asking about what is more efficient. I'm just wanting to know the functionality of what/how Filter is doing its thing in this context

sergiom sergiom · Accepted Answer · 2020-11-01T16:29:13

You are fundamentally correct: what you call a "pre-filter" is actually the filter context set by the Power BI visual.

What happens is that FILTER is evaluated in the filter context set by the Power BI visual, that contains the current CustID.

Therefore FILTER iterates over the Customers table already filtered by the current CustID, that is to say, just one row.

Then it checks the FILTER condition, if it doesn't match, the row is not returned and therefore the FILTER returns the empty set.

When the current CustID matches with the FILTER filter condition, then FILTER returns that row.

Using ALL(Customers) inside FILTER will return the full Customers table ignoring the filter set by the Power BI visual, and therefore FILTER will always return one row, with CustID = 2

That said, a better implementation of this code would be

SumMeasure =
CALCULATE(
    SUM( Sales[SaleAmt] ),
    Customers[CustID] = 2,
    REMOVEFILTERS( Customers )
)

since it uses a filter on a single column instead of the full table and it obtains the same result we get applying filter on ALL( Custoemers ) thanks to the REMOVEFILTERS modifier, that removes the outer filter context on the full Customers table. And the result is the same that we obtain using FILTER on ALL( Customers )

Why does Calculate with Filter return this result?

2 Answers