When is null not a null?

Recently there have been some discussions on the forums and in other areas concerning how PowerPivot handles nulls. As this has changed between CTP3 (back in November) and the upcoming RTM of the product, I thought that it would be a good posting. Here is the situation: suppose you have a simple calculated measure:

m1 = SUM(c1) / SUM(c2)

If c2 is NULL, then then you get #NUM as a result. No worries, pretty much expected. However, it is difficult to trap when this is happening. For example, with CTP3:

m1 = IF (SUM(c2) = 0, 0, SUM(c1) / SUM(c2))

You can use IFERROR and ISERROR but they are slower and have more overhead and should be avoided if possible:

m1 = IFERROR((   SUM(c1) / SUM(c2)   ), 0)

-or-

m1 = IF(ISERROR(   SUM(c1) / SUM(c2)   ), 0, (  SUM(c1) / SUM(c2)   ))

. . . however, regardless, m1 keeps returning #NUM, not 0. In CTP3 PowerPivot does not treat infinity as an error despite #NUM being displayed in Excel.  This is effectively what is going on in this example, as NULL is being converted to zero (division by zero then yields infinity). The RTM version of PowerPivot will treat such a scenario as an error and IF/ IFERROR/ ISERROR will behave as expected.

(My thanks to Marius Dumitru, Russell Christopher and Brandon Unger for providing both the examples above and the answer.)

Enjoy.

_-_-_ Dave