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

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. 

The IF function

The IF function is one of the logical functions, returning 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 logical expression that evaluates to either TRUE or FALSE
• value_if_true: this is required and represents the value to return if the condition is true
• value_if_false: this is an optional argument that is returned if the logical test is FALSE.  If omitted, BLANK is returned.

How it works:

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

It should be noted that:

• the IF function can handle different data types for value_if_true and value_if_false, but it will try to return a single data type if possible
• for multiple conditional branches, consider using the SWITCH function for cleaner code
• the IF function is often used with other DAX functions like AND, OR, NOT, and ISERROR for complex logic.

Let’s consider the following Table call tbl_grades:

We may create DAX to determine whether the students completed their final examinations with a score of five [5] or higher:

In the EVALUATE statement, we construct the following:

• the ADDCOLUMNS function is used to add a new column named Final Result
• the first argument in this function is the table  tbl_grades to which you want to add a new column
• the second argument is a list of expressions that define the new column to be added.  Each expression has two [2] parts:
  • the name of the new column (e.g. Final Result)
  • the IF function that evaluates whether the Grade column is bigger than five [5]
    • if the result is TRUE the result will display “Passed”
    • if the result is FALSE the result will display “Failed”.

In the EVALUATE statement, we summarised the IF result values in the new Final Result column:

Important notes:

• the IF function can be used in calculated columns, measures and table filters
• it is important to consider the data types of the values you're working with to ensure reliable results
• indenting nested IF functions may improve readability, but you should consider alternative feedback where possible.

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.