Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Calculated column with condition returns wrong results (DAX)

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

Columns.JPG

 

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:

 

Results.JPG

 

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

 

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

 

 

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

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 🙂


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

4 REPLIES 4
OwenAuger
Super User
Super User

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 🙂


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
Anonymous
Not applicable

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

 

George.

Sean
Community Champion
Community Champion

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

 

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

@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].

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.