Forum Discussion
Filter from context transition vs previous external filter on same field
The CALCULATE function performs a context transition. A column filter (Date[Date]) then overwrites an existing filter over the same column.
The lack of CALCULATE explains what you observe when you see 3 in all the rows.
The result you expect can be obtained by applying KEEPFILTERS. In order to use KEEPFILTERS vs. context transition it has to be applied over the table expression of the iterator.
CalculatedTable = CALCULATETABLE ( ADDCOLUMNS ( KEEPFILTERS ( ALL ( Table1[Date] ) ); "Result"; CALCULATE ( SUM ( Table1[Amount] ) ) ); FILTER ( Table1; Table1[Date] = DATE ( 2018; 03; 01 ) ) )
Cool. Your latest example is still consistent with my hypothesis.
FILTER ( Table1; Table1[Date] = DATE ( 2018; 03; 01 ) )
returns a one-row table
Date Amount
01/03/2018 3
so we have "outer" filters on both columns, [Date] and [Amount]. When we get to the CALCULATE( ), the inner filter on [Date] (resulting from the context transition) overrides the outer filter. But we still have a filter active on [Amount] and there's a sole row with Amount = 3 so only in that row is the SUM( ) non-blank.
By the way, I'm not really interested in getting to any result in particular but rather in an explanation to the behaviour we're seeing. The examples are a means to an end.
Thanks
