Conditional Values and Testing for Errors

You can use DAX functions to test the values in the data that result in different values based on a condition. For e.g., you can test the yearly sales amount and based on the result, label resellers either as Preferred or Value.

You can also use DAX functions for checking the range or the type of values, to prevent unexpected data errors from breaking calculations.

Creating a Value Based on a Condition

You can use nested IF conditions to test values and generate new values conditionally. Following DAX functions are useful for conditional processing and conditional values −

IF (<logical_test>,<value_if_true>, [<value_if_false>]) − Checks if a condition is met. Returns one value if the condition is TRUE and returns another value if the condition is FALSE. Value_if_false is optional, and if omitted and the condition is FALSE, the function returns BLANK ().

OR (<logical1>,<logical2>) − Checks whether one of the arguments is TRUE to return TRUE. The function returns FALSE if both arguments are FALSE.

CONCATENATE (<text1>, <text2>) − Joins two text strings into one text string. The joined items can be text, numbers, or Boolean values represented as text or a combination of those items. You can also use a column reference, if the column contains appropriate values.

Testing for Errors within a DAX Formula

In DAX, you cannot have valid values in one row of a calculated column and invalid values in another row. That is, if there is an error in any part of a calculated column, the entire column is flagged with an error and you must correct the DAX formula to remove the errors that result in invalid values.

Some common errors in DAX formulas are −

• Division by zero.
• Argument to a function is blank while the expected argument is numeric value.

You can use a combination of logical and information functions to test for errors and always return valid values to avoid returning errors in a calculated column. Following DAX functions help you in this.

ISBLANK (<value>) − Checks whether a value is blank and returns TRUE or FALSE.

IFERROR (value, value_if_error) − Returns value_if_error if the expression in the first argument results in an error. Otherwise, returns the value of the expression itself.

Both the return value of the expression and value_if_error must be of same data type. Otherwise, you will get an error.