How does my CALCULATE function formula in a Calculated Column know to use Row Context?

[mgirvin]

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 is this:
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

P.S. I posted this same question at Msdn forums - SQL Server. I am not sure what the online etiquette is for cross posting the same question at different sites is. Is it okay to cross post?

 

[scottsen]

Calculated columns always operate on a row context (which makes some intuitive sense... its calculated each row independly, and we are used to reference across columns with no aggregate function). Also, in a calculated column there is no filter context.

However, one of the wacky things that calculate does is convert a row context into a filter context. Any interesting experiment would be one calc column with : SUM(fSales[Units]) and one with CALCULATE(SUM(fSales[Units])). The former gives you ALL rows (there is no filter context), the latter gives you just the 1 row (the 1 row context, not promoted into a 1 row filter context).

 

[XLBob]

Hi Mike, first I want to thank you for your great contribution to the wide Excel community. You Excel videos are awesome and really liked Dueling Excel series.
Let me try to explain how that DAX expression works. Filter function will generate row context, so it will go through each row in disCostDiscounts tables and compare disCostDiscounts[Units] with fSales[Units]. The original row context still applies to fSales[Units] (because it's a calculated column) so DAX knows to look at fSales[Units] in the current row and will always look at fSales[Units] for each run of the iteration process. Calculate can only convert row context to filter context but not the other way around.

 

[mgirvin]

scottsen and XLBob,

Thank you so much for replying! It took me a while to get back because I have been in bad health lately...

So, to summarize and add a fourth point that just helps me in my learning proccess:

1) FILTER will generate Row Context because it is an Iterator function
2) Original Row Context also still applies because it is a Calculated Column
3) CALCULATE can convert Row Context to Filter Context, but not the reverse
4) EARLIER function is not needed because the same column is not in both the FILTER function and the original Row Context.

Does that seem correct?

 

[scottsen]

#4 is worded a bit weird for me, but I guess... Yes?

You have a row context on fSales[Units] (cuz its a calc column). You then create another over on disCostDiscounts via the FILTER(). So... you never "hid" the one on fSales, such that an EARLIER would be needed to get back to it.

 

[mgirvin]

scottsen,

Thanks for the reply! But yes, #4 was from left field. As I am learning I am just try to fit all the pieces together. EARLIER is not applicable here at all.

#1-3 were okay?

 

[scottsen]

They strike me as true

 

[mgirvin]

Thanks for your help, scottsen!