Calculated column, measures and evaluation context

[Anonymous]

Hi,

Wanted help something very basis related to evaluation contexts.

To illustrate, I have 3 tables as hown below:

Table 1: Base

Table 2: Purchase

Table 3: Sales

The base table is the dimension table with item name being connected via one-to-many relationship to Purchase and Sales tables.

I created the following measure (in Purchase table) and calculated columns (in Sales table)

Measure in Purchase table             --> Pur_Amt = SUM(Purchase[Value])

Calculated column 1 in sales table --> Check_1 = Purchase[Pur_Amt]

Calculated column 2 in sales table --> Check_2 = SUM(Purchase[Value])

In my mind, check 1 and check 2 were supposed to return the same value as the only difference was that one is a measure and other is just the expanded version of the same measure.

The sales table now looks like below:

By instinct, I know what are the numbers being displayed. But I really wanted to understand how the contexts are being evaluated for each cell.

Thanks in advance

[Anonymous]

Thanks for your reply. Maybe I need to elaborate more. My issues are:

1. Since there is no direct relationship between Sales and Purchase tables, how is Purchase table being filtered when Check_1 is being evaluated?

2. Just because Check_1 is made equal to a measure and Check_2 is essentially the same as Check_1 but in an expanded form, why are the values different?

[Dangar332 · Accepted solution]

hi, @Anonymous 

in check_2 you gave only row context(because of calculated column )  you miss filter context 

so when you wrap your code in calculate () it give filter context beacuse calculate() is filter modifier.

context transition happen only and only when both   1.row context and   2.filter context present in code.

that's why in Check_2 you get 165 becuase of missing of filter context
when you wrap your code in CALCULATE() it give row context.

and it use expanded table that's why it evaluate even direct relationship is not present

in Check_1 you use measure
and  in measure Calculate() present Internally that's why don't need to use Calculate(). 

[Anonymous]

Great. This is what I was looking for. Just one doubt remaining. When context transition happens in Check_1, how can the filters propagate upwards from the 'many' side (i.e. Sales table) of a 'one to many' relationship and that too towards a table with which there is no direct relationship (i.e. towards Purchase table)?

[Dangar332]

hi, @Anonymous 

wrap your measure in calculate()

like 
calculate(SUM(Purchase[Value]))