1

I am having 2 codes as shown below. The goal is to remove the filter from the filter context. VALUES is used to re-instate the filter.

  1. Approach 1:

    AVERAGEX( VALUES('Sales Order'[Sales Order]), [Revenue] )

  2. Approach 2:

    CALCULATE( AVERAGEX( 'Sales Order', [Revenue] ), VALUES('Sales Order'[Sales Order]) )

What is the difference between these approaches?

variable
  • 8,262
  • 9
  • 95
  • 215
  • 1st. neither one is removing any filter. 2nd. Is 'Sales Order'[Sales Order] a PK in 'Sales Order' table? – sergiom Dec 23 '20 at 12:58

1 Answers1

0

The answer to the question: "what is the difference between using or avoiding CALCULATE?" is that CALCULATE (and CALCULATETABLE) is the only function to change a filter context. Other techniques do not change the filter context.

The two formulas in the post are not changing the filter context. But the first one computes the average [Revenue] per 'Sales Order'[Sales Order] in the current filter context

The second one computes the average of [Revenue] computed per each row of the 'Sales Order' table, that generally is different, unless 'Sales Order'[Sales Order] was the PK of the 'Sales Order' table.

Also, the approach n. 2 is usually wrong, since it would misbehave if duplicate rows exist in the 'Sales Order' table, since a context transition happens when evaluating [Revenue], therere the measure would use all the duplicated rows matching the filter instead of only the currently iterated one.

sergiom
  • 4,791
  • 3
  • 24
  • 32
  • What is the purpose of VALUES in approach 1? Is it same as AVERAGE( [Revenue] ) – variable Dec 27 '20 at 18:13
  • Please can you elaborate on "unless 'Sales Order'[Sales Order] was the PK of the 'Sales Order' table." - how does PK affect this calculation. – variable Dec 27 '20 at 18:16
  • if it is the Primary Key then each row has a different value for 'Sales Order'[Sales Order]. If not, then a 'Sales Order'[Sales Order] can be repeated over multiple rows. VALUES() in approach 1 is used as a table function that returns the distinct values of 'Sales Order'[Sales Order]. This means that if 'Sales Order'[Sales Order] appears on more than one row, it is returned only once. – sergiom Dec 27 '20 at 18:44
  • Why does https://learn.microsoft.com/en-us/dax/all-function-dax#example-2 have to use CALCULATE instead of the approach used in https://learn.microsoft.com/en-us/dax/all-function-dax#example-1 ? Both are clearing a filter. can you advise. – variable Jan 12 '21 at 10:17
  • the code in the two examples performs the same calculation. In the one with CALCULATE the ALL funciton is used as a filter modifier that removes the filter over DateTime[CalendarYear]. In the other one ALL is used as a table function that ignores any existing filter. If a filter over another DateTime table column existed, the first one would apply it and give a different result, sinche only the filter over CalendarYear is removed. The second one would ignore that filter too and give the same result. I hope it helps :) – sergiom Jan 12 '21 at 12:30