Calculated column with condition returns wrong results (DAX)

Hi Anonymous,

To get the result you want, you can either

1. Change the calculated column to:
   

   Column =
   IF ( Sheet11[Test1] >= CALCULATE ( [Measure2], ALL ( Sheet11 ) ), "Success" )

   or
2. Change the definition of Measure2 to:
   

   Measure2 =
   CALCULATE ( AVERAGE ( Sheet11[Test2] ), ALL ( Sheet11 ) )

The reason for the original behaviour is:

1. All measures are wrapped in an implicit CALCULATE()
2. In a row context (such as in a calculatd column), CALCULATE results in context transition of the row context to filter context.

So in your original calculated column, the current row became filter context and [Measure2] ended up returning the average of Test2 in across all rows matching the current row, i.e. in this case just the value of Test2 in the current row. So you got "Success" in rows where Test1 >= Test2.

The fixes above use ALL(Sheet11) to ignore the filter context introduced by context transition, so that [Measure2] is evaluated in the context of an unfiltered table Sheet11.

Hope that helps,

Owen :)