# 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:

1. Can you explain the step-by-step calculating process for this formula?
2. 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

• 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 Thursday, October 2, 2014 3:13 PM
• Marked as answer by 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)

• 