Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Calculated column, measures and evaluation context

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

 

 

Need Help
  • Dangar332's avatar
    Dangar332
    2 years ago

    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(). 

     

     

4 Replies

  • Dangar332's avatar
    Dangar332
    Resident Rockstar
    2 years ago

    hi, Anonymous 

    wrap your measure in calculate()

    like 
    calculate(SUM(Purchase[Value]))

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      Dangar332
      Resident Rockstar
      2 years ago

      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's avatar
        Anonymous
        Not applicable
        2 years ago

        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)?