Power Pivot Principles: The A to Z of DAX Functions – IF.EAGER

In our long-established Power Pivot Principles articles, we continue our series on the A to Z of Data Analysis eXpression (DAX) functions.  This week, we look at IF.EAGER. 

The IF.EAGER function

The IF.EAGER function is one of the logical functions and is similar to the IF function.  It returns different values based upon whether a specified condition is TRUE or FALSE.  It employs the following syntax:

This function has three [3] arguments:

• logical_test: this is required and represents any expression that evaluates to either TRUE or FALSE
• value_if_true: this is required and the value to return if the condition is true
• value_if_false: optional argument that's returned if the logical test is FALSE.  If omitted, BLANK is returned.

The key differences between the DAX functions IF and IF.EAGER lie in their evaluation plans and the impact upon performance:

• the IF.EAGER function uses an eager execution plan, where both value_if_true and value_if_false expressions are always evaluated, regardless of the logical_test outcome.  This might be preferable when:
  • both branches need to be calculated regardless of the condition (e.g. side effects within expressions)
  • the performance difference between the branches is minimal or unknown.

IF uses a lazy evaluation plan, where only the expression associated with the TRUE result is evaluated (potentially improving performance if one branch is much more expensive than the other).  Furthermore, it is generally preferred for performance optimisation when one branch is significantly more expensive than the other.  Lazy evaluation avoids unnecessary calculations.
It has the same execution plan as the following DAX expression:

How it works:

•  the IF.EAGER function evaluates the condition
•  if the logical_test is TRUE, it returns value_if_true
•  if the logical_test is FALSE, it returns value_if_false (or BLANK if not specified).

It should be noted that:

•  it can return a variant data type if value_if_true andvalue_if_false have different data types.  Attempts to return a single data type if both are numeric (implicit conversion applies)
• in scenarios where one branch evaluation is significantly more expensive than the other, IF might be more efficient due to lazy evaluation
• IF.EAGER function can be useful when both branch evaluations are necessary regardless of the condition, or when side effects within the expressions are desired
• you should be mindful of potential performance implications if both branches involve complex calculations or database interactions
• IF.EAGER function is not compatible with Excel and is currently only compatible with Power BI.

Let’s consider the following table call tbl_financials:

We may create a DAX to determine whether the Units Sold were higher than the total Average Units Sold:

In the DAX statement, we summarised the IF result values to the new Evaluation column:

Do remember that the IF.EAGER has the same functional behaviour as the IF function, but performance may differ due to differences in execution plans.

Come back next week for our next post on Power Pivot in the Blog section.  In the meantime, please remember we have training in Power Pivot which you can find out more about here.  If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.