Several weeks ago I talked about finding errors in expressions because of the way DAX uses the operators in an expression to automatically convert values to either strings or numbers. I also looked at using the BLANK() function to find and account for missing values in data. But then I got sidetracked by preparing to go to the SQL PASS Summit. So now I want to finish this group of entries by examining how to trap errors and then either correct them or at the very least provide a value that will not cause additional calculation problems.
The first function I want to look at is the IFERROR() function. This function only has two parameters. The first parameter is the expression to test. If the expression evaluates without an error, IFERROR() returns that calculated value. However, if the expression results in an error, the function returns the second parameter. How does this work. Suppose you have a simple expression such as the following for a calculated column named DiscountAmount representing the discount amount of the item:
= PURCHASEORDER[ListPrice] * PURCHASEORDER[DiscountPercent] In most cases, this expression would evaluate correctly. Even if the DiscountPercent or ListPrice values were left blank, we learned when discussion blank data that DAX assumes a value of '0' which in this case could still be considered correct. However, what if one of the values in the DiscountPercent column was not numeric and not a blank? What if the value was a string such as a DiscountPercent value being 'NA'? Then DAX would register an error for the calculated column and not display any of the calculated values. Resolving this problem by visually examining the dataset may work when you have a small dataset with only a few records because you can scroll through the data and quickly find the string value that caused the problem. However, if you have millions of data rows, finding the cause of the error may be just a bit more difficult and time consuming. With DAX we can easily solve this problem by using the IFERROR() function in the expression as shown below: = IFERROR( PURCHASEORDER[ListPrice] * PURCHASEORDER[DiscountPercent], 0)
In this case the IFERROR() function tests the value of the expression and if an error occurs because one or both values in the expression cannot be converted to numeric values, it traps the error and returns the value of the second parameter, rather than returning the product of the expression in the first parameter. On the other hand, returning a value of ‘0’ may not be what you want. After all, a value of ‘0’ implies that no discount is applied. However, in this case, we really don’t know why the discount amount is not calculated, only that it results in an error. Therefore, you may want to return a blank value as in the following expression.
= IFERROR( PURCHASEORDER[ListPrice] * PURCHASEORDER[DiscountPercent], BLANK() )
Using the IFERROR() function works best if the tested expression is also the expression that you want to return if the test expression does not generate an error. But what if the test expression is different from the expression that should be used when the test expression is true? In this case you want to use the ISERROR() function to test for an error. The following expression tests for an error when multiplying the ListPrice times the DiscountPercent in table PURCHASEORDER.
= ISERROR( PURCHASEORDER[ListPrice] * PURCHASEORDER[DiscountPercent] )
By itself, this expression just returns a value of TRUE or FALSE. To use this in an expression that returns something other than just the product of these two values, I will use the IF() function to define what value to return based on the Boolean result of the ISERROR() function. If ISERROR() returns a value of true indicating an error, the IF() expression returns the value of the second parameter. Otherwise, it returns the value in the third parameter.
= IF(ISERROR( PURCHASEORDER[ListPrice] * PURCHASEORDER[DiscountPercent] ), 0, PURCHASEORDER[ListPrice] * (1 - PURCHASEORDER[DiscountPercent]) * PURCHASEORDER[Quantity] )
Testing your expressions may seem much more complex than just entering the expressions you want to calculate. You may also think that performing these tests against every row of a large table must also degrade performance, and you would be correct. In practical use, I do not include a lot of test functions such as those described here unless I encounter a problem with a calculated column or suspect that the data in the column may not be clean. If you cannot trust your data sources, then you really don’t have much choice but to test. Perhaps an alternate approach to adding IFERROR(), ISERROR(), or IF() functions in the calculations is to perform data cleansing first using the DQS tool that is now part of SQL Server. Of course setting up a DQS knowledgebase and then using it to find and correct data issues can also be time consuming. You may even want to consider the application that captures the data to tighten the validation rules that check the data entered into that system trapping errors as they are entered rather than trying to identify and fix them later.
C’ya next time.