Last time I started the conversation about some of the more common types of error you can encounter when developing DAX equations to define columns and measures in Power Pivot. This time, I will look at one specific type of error, the notorious and dreaded empty value in a numeric field.
Empty values in a numeric field are probably the result of a field that does not have a default value for a non-required field when new records are entered. I emphasize the non-required field because even with a default value, it is possible to blank out the default and continue entering the rest of the field values for the record before saving it. By making the field required, the user must enter something. For example, in a sales system, the quantity purchased should never be zero otherwise why would you be entering a sales record in the first place. In fact, in this case, you probably want to require that the value entered be a positive integer greater than zero.
On the other hand, suppose that you have a Discount Amount field in the record. Most of the time, the discount amount is zero, but occasionally it might be more than zero. That would be fine as long as a value was required, but suppose it is not and the user leaves the discount amount field blank. What does that really mean? Is the discount amount really zero and the user therefore just skipped over it to avoid an extra keystroke? Or did the user simply forget to enter the discount amount which is really 10%? Well, DAX will not be able to answer that question. You can only control that through defaults and required value and value validation clauses in the input form. What DAX can do is help you calculate the final sales price given the unit price, the quantity purchases and the discount amount as a percent. The following table shows an example of what a typical data file might look like.
Moving this into the Power Pivot data model, I could calculate the amount due for the record by using the following equation:
=[Sales Price]*[Sales Quantity]*(1-[Discount Amount])
DAX has no problem with this expression even with blanks in the numeric column because it treats the blank or empty values as zeros. Therefore even though the user did not enter a discount amount, DAX assumes that the discount should be 0%. You can see this in the results of the following figure which shows the calculated column Amount_Due.
However, what if our problem was a little different? What if we knew the Sales Quantity, the Amount Due and the Charge Ratio (defined as the percent of the amount actually due rather than the discount). In this case, most items would have a charge ratio of 100% and only those items on discount would have a charge ratio less than 100% as shown in the following figure.
This time when I bring this data into the Power Pivot data model and attempt to calculate the original item price for each row, I might simply want to divide the Amount Due by the Charge Ratio and then divide that amount by the Sales Quantity. My equation might look like this.
=[Amount Due]/[Charge Ratio]/[Sales Quantity]
As shown in the following figure, most of the rows calculate the item price correctly except for the two rows in which the Charge Ratio was not entered. Remember that DAX treats empty numeric values as zeros. This means that the above equation would attempt to divide the [Amount Due] value by zero resulting in the value of Infinity (yes, this is a special value in DAX rather than reporting an error or NA).
Since I do not have the ability to correct the source data before bringing it into DAX, I must use DAX to account for these missing values. To do that I can use the special value returned by the function: BLANK(). I need to test each value of [Charge Ratio] against this value of BLANK() to locate missing values and ‘modify’ the equation to treat the missing value as if it were 100% (or a value of 1.0). I can do this with DAX’s IF clause as in:
=[Amount Due]/IF([Charge Ratio]=BLANK(),1,[Charge Ratio])/[Sales Quantity]
This IF function evaluates the first expression, [Charge Ratio]=BLANK(), and returns a Boolean true or false value. If the expression returns True, the second expression in the IF() function gets returned. Otherwise, the IF() function returns the third expression which is the value [Charge Ratio]. In this case, I am simply returning a value of 1 when the Change Ratio is blank assuming that there was no discount on this item. The following table from the Pivot Data Model shows that now all of the original item prices are properly calculated.
So here are some other interesting points about using the BLANK() function. DAX evaluates any expression with BLANK() in it and returns a result of BLANK() if BLANK() is used in anything other than addition or subtraction. When BLANK() appears in an expression involving addition or subtraction, DAX effectively ignores it. An interesting exception is 0/BLANK() which returns the special value of NaN. Also in a logical expression where either TRUE or FALSE is compared to BLANK() the value of TRUE or FALSE will always will regardless of whether the logical connect is an AND (&&) or an OR (||).
Next time, I will dive into some other DAX expressions to find other types of potential data errors. By the way, the PASS Summit in Charlotte, NC is coming up real soon and I’ll be there. I will be presenting two sessions during the week. If you have chance, stop by to say ‘hello’.
C’ya next time.