Power BI Antipatterns #11: Hiding 0 in DAX

Measures that return 0 may be undesirable in certain cases, like when 0 isn’t an interesting value and you’re only interested in non-zero values. In Power BI Antipatterns #7, we saw how you can show 0 instead of BLANK; this blog post shows a common (bad) way of hiding 0 and two better ways.

Here’s a typical antipattern implementation:

Example bad = 
IF(
    SUMX(
        Sales,
        IF(
            RELATED('Product'[Color]) = "Red",
            Sales[Unit Price] * Sales[Order Quantity],
            0
        )
    ) = 0,
    BLANK(),
    SUMX(
        Sales,
        IF(
            RELATED('Product'[Color]) = "Red",
            Sales[Unit Price] * Sales[Order Quantity],
            0
        )
    )
)

Note how in the formula above, we’re repeating a good chunk of code. The actual formula inside IF doesn’t matter; what matters is that it’s repeated exactly within the same formula. First we check if some value is zero, and if it is, then we’re returning BLANK, otherwise we return that value.

Better ways

Experienced DAX writers are probably itching to say that a variable would be appropriate here, and I agree of course:

Example good 1 = 
VAR RedSales =
    SUMX(
        Sales,
        IF(
            RELATED('Product'[Color]) = "Red",
            Sales[Unit Price] * Sales[Order Quantity],
            0
        )
    )
VAR Result =
    IF(
        RedSales <> 0,
        RedSales
    )
RETURN
    Result

Note: why am I using a Result variable when I could just write its contents after RETURN? It’s a good practice for debugging purposes: in case I need to return the result of a different variable, I only need to change the variable reference in RETURN without copying/pasting larger sections of code.

In addition to using a variable, I flipped the check in IF: instead of checking if something is 0, I’m checking whether something isn’t equal to 0, and that allows me not to specify the third parameter in IF, because a missing third parameter in IF is the same as BLANK, just shorter.

The only problem with the formula above is its performance: it’s suboptimal. In case the performance is more important than readability, you can use the DIVIDE trick, which I learned from Greg Baldini:

Example good 2 = 
VAR RedSales =
    SUMX(
        Sales,
        IF(
            RELATED('Product'[Color]) = "Red",
            Sales[Unit Price] * Sales[Order Quantity],
            0
        )
    )
VAR Result =
    RedSales * DIVIDE(RedSales, RedSales)
RETURN
    Result

What’s going on here? Why are we dividing a value by itself and then multiply by the same value? If a value is

• 0 or blank, then DIVIDE will return BLANK; 0 multiplied by BLANK is BLANK, which is what we want in this case.
• Non-0, then mathematically, DIVIDE will return 1; any value multiplied by 1 is the value itself.

Somewhat surprisingly, this formula performs better because there’s no IF — just maths.

Again, the problem of [Example good 2] is its readability: no matter what you say, it’s more difficult to read than [Example good 1], however, [Example good 2] is more performant. So you should decide between performance and readability yourself and choose the appropriate technique, now that you know both ways.

Download sample file: Episode 11.pbix

To be continued…