Forum Discussion

AlB's avatar
AlB
Icon for Community Champion rankCommunity Champion
7 years ago
Solved

Filter from context transition vs previous external filter on same field

Hi all, We have Table1 as follows: Date Amount 01/01/2018 1 01/02/2018 0 01/03/2018 3 01/04/2018 4 01/05/2018 5 01/06/2018 6 01/07/2018 0 01/08/2018 0 01/09...
Need Help
marcorusso's avatar
marcorusso
Icon for Most Valuable Professional rankMost Valuable Professional
7 years ago

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 ) )
)
AlB's avatar
AlB
Icon for Community Champion rankCommunity Champion
7 years ago

marcorusso

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 )
)

 

 

 

  • marcorusso's avatar
    marcorusso
    Icon for Most Valuable Professional rankMost Valuable Professional
    7 years ago

    Correct.

     

    Marco Russo - SQLBI