Power BI Antipatterns #7: ISBLANK

If you want to return 0 instead of BLANK, do you need to check if the result is BLANK first? I’m covering this and a few related issues in this blog post.

First of all, here’s a typical antipattern of the sort I’d like to discuss today:

Example bad = 
CALCULATE(IF(ISBLANK([Sales System 1]),0,[Sales System 1]))
+ CALCULATE(IF(ISBLANK([Sales System 2]),0,[Sales System 2]))
+ CALCULATE(IF(ISBLANK([Sales System 3]),0,[Sales System 3]))
+ CALCULATE(IF(ISBLANK([Sales System 4]),0,[Sales System 4]))

Apart from our old foe from Antipatterns #6, CALCULATE in a measure with no filter, what’s wrong with the formula? At minimum, we could save each measure reference in a variable to avoid calling each measure twice.

After having a closer look, is it necessary? What are we trying to do here? The essence is “if something is BLANK, return 0, otherwise return that something”. So we just want to ensure we return 0 instead of BLANK.

I’ve seen the ISBLANK and similar checks many times, and my best guess as to why people write DAX like that is they’re former SQL users, who think that NULL in SQL is the same as BLANK in DAX. It’s not:

In SQL, 1 + NULL = NULL
In DAX, 1 + BLANK = 1

Therefore, the ISBLANK check is unnecessary, and the formula above can be simplified as follows:

Example good = 
[Sales System 1]
+ [Sales System 2]
+ [Sales System 3]
+ [Sales System 4]
+ 0

Note: in some cases, if you want to return 0 instead of BLANK, adding 0 may not be the best way. For more details, see How to return 0 instead of BLANK in DAX – SQLBI.

Some people use the “= BLANK()” check instead of ISBLANK. Is it equivalent? It’s not. Run the following query in DAX.do, and you’ll see for yourself:

EVALUATE
ADDCOLUMNS (
    DATATABLE (
        "Description", STRING,
        "Text", STRING,
        {
            { "Text 0", "0" },
            { "Empty string", "" },
            { "Blank", BLANK () }
        }
    ),
    "Is Blank", ISBLANK ( [Text] ),
    "Equal to Blank", [Text] = BLANK ()
)

The results are as follows:

As you can see, ISBLANK is stricter than “= BLANK()”. If you want to use an equation and replicate the results of ISBLANK, you’ve got to use the strictly equals operator like so:

EVALUATE
ADDCOLUMNS (
    DATATABLE (
        "Description", STRING,
        "Text", STRING,
        {
            { "Text 0", "0" },
            { "Empty string", "" },
            { "Blank", BLANK () }
        }
    ),
    "Is Blank", ISBLANK ( [Text] ),
    "Equal to Blank", [Text] = BLANK (),
    "Strictly Equal to Blank", [Text] == BLANK ()
)

If you’re interested in functions that deal with BLANKs in DAX, see Different blanks in DAX.

To be continued…