Calculated column with condition returns wrong results (DAX)

[Anonymous]

Hello, I am trying to use a seemingly straightoforward condition to calculate a new column but I keep getting unexpected results. My database is fairly large but I have created a small dataset where the same issue occurs.

Here's my example:

I have the following two columns

I then created a New Measure:

Measure2=AVERAGE(Sheet11[Test2])

(i.e. the average of my second column, the answer being 5)

I then added a New Column:

Column=if(Sheet11[Test1]>=[Measure2],"Success")

I expected this to show Success to all values in column Test1 that are 5 or above. However, this is what I get:

... which doesn't make any sense!!

Am I missing something blatantly obvious? Any help much appreciated!!

[OwenAuger · Accepted solution]

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 🙂

[Anonymous]

Thanks a million Owen for the solution and the great explanation! This did the trick!

George.

[Sean]

@Anonymous An alternative would be to use AVERAGEX like this...

Column = IF ( Sheet11[Test 1] >= AVERAGEX ( ALL ( Sheet11 ), Sheet11[Test 2] ), "Success" )

[ImkeF]

@OwenAuger Beautiful explanation of context transition in DAX!

If the only purpose of Measure2 is to help calculating the desired column (or you are interested in a language comparison) the M-solution has advantages:

let
    Source = #table({"Test 1", "Test 2"}, {{10, 10}, {9, 14}, {8, 4}, {7, 3}, {6, 4}, {5, 6}, {4, 3}, {3, 2}, {2, 1}, {1, 3}}),
    Measure2 = List.Average(Source[Test 2]),
    Column = Table.AddColumn(Source, "Column", each if [Test 1] >= Measure2 then "Success" else null)
in
    Column

There is no need for a separate measure as the average will be calculated as a temporary step that can be referenced but must not be shown anywhere (and maybe later hidden from client tools).

Or even more compact:

let
    Source = #table({"Test 1", "Test 2"}, {{10, 10}, {9, 14}, {8, 4}, {7, 3}, {6, 4}, {5, 6}, {4, 3}, {3, 2}, {2, 1}, {1, 3}}),
    Column = Table.AddColumn(Source, "Column", each if [Test 1] >= List.Average(Source[Test 2]) then "Success" else null)
in
    Column

This can be done easier using the UI only. Simply add a custom column and paste the following code into the custom column formular window:

if [Test 1] >= List.Average(Source[Test 2]) then "Success" else null

Within a custom column in M/the query editor a reference to a column like [Test 1] is equivalent to an evaluation in row context in DAX, so only the value of the current row will be considered. The trick to adress the column "Test 2" as a whole lies in referencing it with a preceding table name like this: Source[Test 2].