Working with conditional functions in DAX

Conditional functions in DAX (Data Analysis Expressions) are essential tools for performing conditional operations in your data models. These functions allow for more dynamic calculations, such as creating conditional columns, and measures, or performing data filtering based on specific criteria.

Conditional functions are integral to decision-making processes within data models, providing the foundation for complex conditional logic. They help with calculated columns and measures, enabling complex business rules and custom metrics. Conditional functions also help model scenarios where decisions need to be made based on data, such as determining whether a customer qualifies for a discount or flagging high-risk transactions.

IF is the key conditional function in DAX

The IF function is one of the most basic conditional functions in DAX. It evaluates a condition and returns one value if the condition is true and another value if the condition is false.

IF(condition, value if true, [value if false])

Sales label = IF(transactions[Units sold] > 20, "High", "Low")

Furthermore, AND and OR functions are used to combine multiple logical conditions. AND returns TRUE if all conditions are true, while OR returns TRUE if at least one condition is true.

IF(AND(<condition 1>, <condition 2>), value if true, value if false)
IF(OR(<condition 1>, <condition 2>), value if true, value if false)

officeAccessoryTotal = SUMX(
 transactions,
 IF(
    OR(transactions[Category] = "Accessories", 
    transactions[Category] = "Office Supplies"
   ),
   transactions[Revenue],
 0)
)

Result: 11K

Consequently, the NOT function is commonly used to reverse the result of a logical condition. This function is used to check if the condition is FALSE.

NOT(<condition>)

HighMediumNonDiscountSale = SUMX(
  transactions,
  IF(
     AND(NOT(transactions[Sales label] = "Low"), 
     transactions[Discount] = 0), 
     transactions[Revenue], 
     0
  )
)

Result: 250

Using SWITCH for multiple conditions

The SWITCH function is a powerful alternative to nested IF statements in DAX. It evaluates an expression against a list of possible values and returns the result corresponding to the matching value. If no match is found, an optional default value can be returned.

SWITCH(<expression>, <value 1>, <result 1>, <value 2>, <result 2>, ..., [<else>])

#Explanation
<expression>: The expression to evaluate.
value 1, value 2: Possible values of the expression.
result 1, result 2: Results corresponding to each value.
else: (Optional) The result if no values match.

Sales label = SWITCH(
    TRUE(),
    transactions[Units sold] > 50, "High", 
    transactions[Units sold] > 20, "Medium", 
    "Low"
)

Information functions based on type, state or value of the result

IFERROR function returns a specified value if the expression results in an error; otherwise, it returns the value of the expression. It is useful in cases such as division by 0.

IFERROR (<expression>, <result if error>)

• ISBLANK(<value>): Returns TRUE if the value is blank.
• ISNUMBER(<value>): Returns TRUE if the value is a number.
• ISTEXT(<value>): Returns TRUE if the value is text.
• ISERROR(<value>): Returns TRUE if the value results in an error.

Furthermore, the CONTAINS function checks if a specified value exists within a column of a table.

CONTAINS (<table>, <column name>, <value to find>)