Forum Discussion

cliveb2016's avatar
cliveb2016
New Member
8 years ago
Solved

Why does CALCULATE behave like this when using FILTER?

Hi I have a question related to the tables discussed here:

 

http://www.excelnaccess.com/context-transition-using-calculate/

 

Why does the calculated column:

 

Column = CALCULATE(SUM(Table1[earnings]),Table1[Year] = 2011)

Behave differently to

 

Column = CALCULATE(SUM(Table1[earnings]),FILTER(Table1, Table1[Year] = 2011))

 

The first returns the total for each one correctly whereas the second returns the total for all athletes for the year 2011 rather than the athlete in that row.

 

I guess for some reason the use of 

 

FILTER

 

Has some how gotten rid of the row context in the calculated column but I thought that CALCULATE would convert the row context into the FILTER context and give back the same result???

 

Can anyone explain?

 

Bonus question: Do calculated columns induce a row context (i.e. when you use a calculated column does it evaluate the column formula for each row? If so why do we get the behaviour as shown in measure 2?)

 

Thank you so much!

 

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    cliveb2016

     

    This is a lovely question

    My article only covers the basics

     

    Also see what happens with

     

    Column = CALCULATE(SUM(Table1[earnings]),Table1)

     

    Actually it has to do with the order in which arguments are evalauted.
    Filter Parameter of the Calculate is exected first

    Please see this  article

     

    https://www.sqlbi.com/articles/order-of-evaluation-in-calculate-parameters/

     

    So when you introduce Filter(Table1), it introduces an UnFiltered Table in a Calculated Column (ROW CONTEXT). So it has the impact of removing the FILTERS

    When you use

    Column = CALCULATE(SUM(Table1[earnings]),Table1[Year] = 2011


    This is internally transformed by DAX Engine into following formula'

    See the article https://www.sqlbi.com/articles/filter-arguments-in-calculate/

     

    Column = CALCULATE(SUM(Table1[earnings]),Filter(all(Table1[Year]),Table1[Year] = 2011))

    So this formula retains all other filters except for Table1 Year which you modify to be 2011

     

    Now if you want your second formula to give same results, you would have to use

     

    Column = CALCULATE(SUM(Table1[earnings]),FILTER(RelatedTable(Table1), Table1[Year] = 2011))

     

1 Reply

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    cliveb2016

     

    This is a lovely question

    My article only covers the basics

     

    Also see what happens with

     

    Column = CALCULATE(SUM(Table1[earnings]),Table1)

     

    Actually it has to do with the order in which arguments are evalauted.
    Filter Parameter of the Calculate is exected first

    Please see this  article

     

    https://www.sqlbi.com/articles/order-of-evaluation-in-calculate-parameters/

     

    So when you introduce Filter(Table1), it introduces an UnFiltered Table in a Calculated Column (ROW CONTEXT). So it has the impact of removing the FILTERS

    When you use

    Column = CALCULATE(SUM(Table1[earnings]),Table1[Year] = 2011


    This is internally transformed by DAX Engine into following formula'

    See the article https://www.sqlbi.com/articles/filter-arguments-in-calculate/

     

    Column = CALCULATE(SUM(Table1[earnings]),Filter(all(Table1[Year]),Table1[Year] = 2011))

    So this formula retains all other filters except for Table1 Year which you modify to be 2011

     

    Now if you want your second formula to give same results, you would have to use

     

    Column = CALCULATE(SUM(Table1[earnings]),FILTER(RelatedTable(Table1), Table1[Year] = 2011))