Answered by:
How does this CALCULATE function formula in a Calculated Column know to use Row Context?
Question

Dear Team,
I am creating a Calculated Column to simulate VLOOKUP Approximate Match. The goal of the formula is to retrieve the Cost discount based on number of units sold. I have a table named “disCostDiscounts”, where the first column is Units and the second column is Discounts. The table looks like this:
Units
Discounts
0
0
26
0.02
51
0.03
76
0.05
100
0.075
500
0.1
1000
0.15
I also have transactions table named “fSales”, where one of the columns is named Units.
Here is my Calculated Column formula in the “fSales” table:
=CALCULATE(MAX(disCostDiscounts[Discounts]),FILTER(disCostDiscounts,disCostDiscounts[Units]<=fSales[Units]))
My question are:
 Can you explain the stepbystep calculating process for this formula?
 The part that I am not understanding is this: I thought that inside the CALCULATE function, CALCULATE always ran “Filter Context”, so how is it that the formula knows that for “fSales[Units]” it should be doing “Row Context”? Does the fact that the formula is in a Calculated Column which automatically creates “Row Context” override the “Filter Context” inside CALCULATE? Or is this a case where CALCULATE does its “Context Transition”?
Sincerely, Mike "Trying To Learn PowerPivot, but it is Really Hard" Girvin
Sunday, August 24, 2014 8:54 PM
Answers

In a table (whether a calculated column in a physical table, or in a table function, e.g. with ADDCOLUMNS() ), CALCULATE transforms row context into filter context which can then be modified based on arguments 2  N of CALCULATE().
In a pivot, CALCULATE internalizes all external filter context (rowfilters, columnfilters, table filters and slicers) to calculate the value in a specific pivot cell.
 Proposed as answer by Michael Amadi Thursday, October 2, 2014 3:13 PM
 Marked as answer by Michael Amadi Monday, October 13, 2014 12:22 PM
Thursday, October 2, 2014 3:05 PM
All replies

Mike, is this still an issue?
Thanks!
Ed Price, Azure & Power BI Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)
Answer an interesting question? Create a wiki article about it!Wednesday, October 1, 2014 11:52 PM 
In a table (whether a calculated column in a physical table, or in a table function, e.g. with ADDCOLUMNS() ), CALCULATE transforms row context into filter context which can then be modified based on arguments 2  N of CALCULATE().
In a pivot, CALCULATE internalizes all external filter context (rowfilters, columnfilters, table filters and slicers) to calculate the value in a specific pivot cell.
 Proposed as answer by Michael Amadi Thursday, October 2, 2014 3:13 PM
 Marked as answer by Michael Amadi Monday, October 13, 2014 12:22 PM
Thursday, October 2, 2014 3:05 PM