Avoid converting BLANKs to values

As a data modeler, when writing measure expressions you might come across cases where a meaningful value can't be returned. In these instances, you may be tempted to return a value—like zero—instead. It's suggested you carefully determine whether this design is efficient and practical.

Consider the following measure definition that explicitly converts BLANK results to zero.

Sales (No Blank) =
IF(
    ISBLANK([Sales]),
    0,
    [Sales]
)

Consider another measure definition that also converts BLANK results to zero.

Profit Margin =
DIVIDE([Profit], [Sales], 0)

The DIVIDE function divides the Profit measure by the Sales measure. Should the result be zero or BLANK, the third argument—the alternate result (which is optional)—is returned. In this example, because zero is passed as the alternate result, the measure is guaranteed to always return a value.

These measure designs are inefficient and lead to poor report designs.

When they're added to a report visual, Power BI attempts to retrieve all groupings within the filter context. The evaluation and retrieval of large query results often leads to slow report rendering. Each example measure effectively turns a sparse calculation into a dense one, forcing Power BI to use more memory than necessary.

Also, too many groupings often overwhelm your report users.

Let's see what happens when the Profit Margin measure is added to a table visual, grouping by customer.

The table visual displays an overwhelming number of rows. (There are in fact 18,484 customers in the model, and so the table attempts to display all of them.) Notice that the customers in view haven't achieved any sales. Yet, because the Profit Margin measure always returns a value, they are displayed.

Note

When there are too many data points to display in a visual, Power BI may use data reduction strategies to remove or summarize large query results. For more information, see Data point limits and strategies by visual type.

Let's see what happens when the Profit Margin measure definition is improved. It now returns a value only when the Sales measure isn't BLANK (or zero).

Profit Margin =
DIVIDE([Profit], [Sales])

The table visual now displays only customers who have made sales within the current filter context. The improved measure results in a more efficient and practical experience for your report users.

Tip

When necessary, you can configure a visual to display all groupings (that return values or BLANK) within the filter context by enabling the Show Items With No Data option.

Recommendation

It's recommended that your measures return BLANK when a meaningful value cannot be returned.

This design approach is efficient, allowing Power BI to render reports faster. Also, returning BLANK is better because report visuals—by default—eliminate groupings when summarizations are BLANK.

Related content

• Learning path: Use DAX in Power BI Desktop
• Questions? Try asking the Power BI Community
• Suggestions? Contribute ideas to improve Power BI