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 ) ) )
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 ) )
)
OK, so in general, a filter resulting from context transition on ColumnA will overwrite any previous filter there was on ColumnA, correct?
Thus in my initial code (see below), the filter on Table1[Date] resulting from context transition within the CALCULATE( ) will overwrite the theretofore existing filter on Table1[Date] (originated in the CALCULATETABLE( )). Correct?
Many thanks
CalculatedTable =
CALCULATETABLE (
ADDCOLUMNS (
ALL ( Table1[Date] ),
"Result", CALCULATE ( SUM ( Table1[Amount] ) )
),
Table1[Date] = DATE ( 2018, 03, 01 )
)