Detecting Expression Errors in DAX


Earlier this week I was talking with my manager about what makes a good programmer. We discussed several factors each of which has some validity.  The one that I want to focus on today is my belief that handling errors gracefully is one trademark of a good programmer. Even programmers who are not always 100% successful at trapping all errors learn from each instance and improve their skills over time. Well if that is true of general programming in VB.NET, C# or any other language, why would you not expect the same to be true in DAX?

In this first of a multi-part series, I want to look at a few selected error types that a DAX programmer might encounter when defining expressions for columns or measures. I’ll limit the error types to syntax related errors such as missing or empty values, mixed data types and invalid arithmetic operations. Logic errors on the other hand are much more complex to identify. Often logic errors hide by generating results that may not at first be identified as wrong such as when you add two column values together rather than subtracting one from the other.

Let’s start by looking at missing values. Suppose you have a series of values in a column such as the following figure:

In this figure, you can see that Carol has no sales in 2013 and Sandra has no sales in 2010. Further suppose that for each year, management has established an expected sales goal for each salesperson and calculates their salary on a base amount plus an incentive amount. Suppose the formula they devise pays each sales person $20,000 per year plus $20,000 times the ratio of their sales to the sales goal of $6,000,000. This formula could be expressed as:

=20000 + C2/6000000 * 20000

Using this formula in our table would result in the following annual salaries for each of our three sales staff.

The first thing to notice is that according to this formula, Carol would receive $20,000 in 2013 and Sandra also received $20,000 in 2010. In both cases, these sales staff were not even employed by the company in those years. This error may only be obvious when the Sales amount column appears next to the Salary column for each year for each sales person. Otherwise, how would someone looking at just a salary report by person and year know that a problem existed?

In this case, I might say that the expression did not check for empty sales amounts and then assign a Salary of $0.

Missing or zero values can be more serious than just an incorrectly calculated value. Another common arithmetic error involves the division of one number by zero. When dividing a value by zero in DAX, a strict interpretation of the math should return a value of infinity. Furthermore, any number divided by infinity should return a value of 0.

Using the above data, suppose I wanted to calculate the percent growth in sales for each of the staff on a year basis. The basic formula is:

Percent Growth = ([Current year sales] – [Last Year Sales]) / [Current Year Sales]

First, we have to account for the fact that sales only go back to 2010 (2011 for Sandra). If [Last Year Sales] is zero, then dividing [Current year sales] by [Current year sales] would result in the interesting, but wrong result of 100% growth. Second, we see that the growth calculation for Carol in 2013 results in a value of #NUM! because Carol did not have any sales in 2013. Therefore this cell attempts to calculated a value by dividing a value by zero.

Finally, there is a question of how DAX automatically converts data to specific data types in expressions. The following expression attempts to add a string value of “1” to a numeric value of 1. Because the connecting operator is a plus sign, DAX attempts to convert any non-numeric value to a number before performing the operation. In this case, DAX returns a value of 2.

“1” + 1 = 2

On the other hand, if I replace the plus sign with the ampersand operator used to concatenate strings, then the result is the string value “11”.

“1” & 1 = “11”

In fact, even if I attempt to concatenate two numeric values, the result is the concatenated string.

1 & 1 = “11”

And just to round things out, adding two string values that can be converted to numbers results in a numeric value.

“1” + “1” = 2

So, in DAX, the data type is not as important as the operator used with those data types. In the following figure [Value1] and [Value2] are defined as strings and [Value3] and [Value4] are defined as numbers.

Now to make the problem more complex, DAX is column based in its operations. Therefore, if an expression between two or more columns fails because the column value could not be converted to a data type required by the operator, DAX displays an error in all the rows of the table, not just the row that has the problem. This can make finding the problem much more difficult especially if you have thousands or millions of rows.

Actually, this all or nothing approach when calculating a column is one of the main aarguments why you may want to consider using error trapping functions in DAX to handle exceptions. Next time I will take a look at some of the ways you can trap potential errors such as these and keep DAX from return bad values or errors.

C’ya next time.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s