Back to Blog

Avoid Converting BLANKs to Values in Power BI - A DAX Discipline

April 26, 2026•7 min read•Michael Ridland

Most Power BI models I have ever audited contain a measure that looks something like this:

Sales YoY = IF(ISBLANK([Sales LY]), 0, [Sales] - [Sales LY])

The intention is good. The author saw a BLANK appear somewhere in a visual, decided it looked unprofessional, and replaced it with zero. The measure now always returns a number. Job done.

The problem is that BLANK and zero mean different things, and the moment you start interchanging them you create bugs that are difficult to track down. This is one of the most common sources of subtle wrongness in Power BI models we walk into. Let me explain why, and what to do instead.

What BLANK actually means in DAX

BLANK in DAX is not zero. It is not null in the SQL sense either, though it behaves a bit like it. BLANK means "there is no value here." It signals an absence of data, not a value of zero.

The distinction matters because BLANK gets special handling throughout DAX:

  • Visuals can be configured to hide rows or columns where all measures are BLANK
  • Sorting treats BLANK as smaller than any value
  • Aggregations skip BLANK rather than treating them as zero
  • Comparisons with BLANK use specific rules that differ from comparisons with zero

When you convert BLANK to zero, you opt out of all of that behaviour. The visual no longer hides empty rows. Sorting changes. Aggregations include the converted zero. The numbers technically still add up, but the model no longer carries the information it used to.

The real cost of "looking neat"

I had a conversation a few years back with a finance manager who was very proud of how clean her reports looked. No empty cells anywhere. Every column populated. Very professional.

The problem became clear when we asked her about a specific product line that showed sales of zero across multiple months. "That's right," she said. "We didn't sell any of those."

Except they had not sold any of those because the product had been discontinued eighteen months earlier and was no longer on the price list. The data layer was returning BLANK because there were no transactions and no inventory. Her measure was converting BLANK to zero, so the discontinued product looked like an active product with no sales. The board was asking why this product was underperforming when in fact it did not exist anymore.

This is the kind of bug that does not show up in unit tests. The number is plausible. The report is clean. Nobody notices until somebody asks a question that depends on the difference.

We see this pattern often when our Power BI consultants come in to do model audits. Defaulting BLANK to zero is one of the most common quality issues in production Power BI deployments.

When converting BLANK is actually justified

I am not saying never convert BLANK. There are cases where it is the right call.

Time-series charts where you genuinely want a continuous line with zeros for periods of no activity. A line chart of website signups by day, where days with no signups should show zero, not skip the day entirely. Here, converting BLANK to zero is fine.

Aggregations where you want to compute averages including periods of no activity. If you are calculating average daily revenue across a year, and some days had no sales, you might want those zero days to count in the denominator. BLANK would skip them, which changes the average.

Mathematical operations where BLANK propagation would cause downstream errors and the business meaning of "no value" is genuinely "zero value." Stock levels are a good example. A product with no inventory record is reasonably treated as zero stock, depending on your business context.

The common thread is that the conversion should be a deliberate choice based on what the metric means, not a default applied because empty cells look bad.

The patterns we actually use

On real client projects, particularly the larger Microsoft Fabric and Power BI builds we work on through our Microsoft Fabric consultants practice, we have a few patterns that come up repeatedly.

Pattern one: preserve BLANK in display measures.

The measure that gets put on a visual should generally return BLANK when there is no data. Let the visual handle the display. Power BI has settings for how to render BLANK cells, and those settings are appropriate for the report context.

Sales = SUM(Sales[Amount])

That is it. Do not wrap it in IF logic. If there are no sales, BLANK is the correct answer.

Pattern two: convert BLANK explicitly when arithmetic requires it.

For year-on-year comparisons, you sometimes need to handle BLANK explicitly because BLANK arithmetic propagates BLANK and that may not be what you want:

Sales YoY = 
VAR CurrentSales = [Sales]
VAR PriorSales = [Sales LY]
RETURN
  IF(
    NOT ISBLANK(CurrentSales) || NOT ISBLANK(PriorSales),
    COALESCE(CurrentSales, 0) - COALESCE(PriorSales, 0)
  )

This returns BLANK only when both periods are BLANK, which is the honest answer. If you sold nothing this year and nothing last year, the YoY comparison is genuinely meaningless and BLANK is correct. If you sold something in either year, the calculation proceeds and BLANK gets treated as zero for arithmetic purposes only.

This is more verbose than the original "convert BLANK to zero" version, but it carries the right information through the calculation.

Pattern three: use DIVIDE to handle BLANK in ratios.

If you are doing a ratio measure, DIVIDE handles BLANK in the denominator naturally, returning BLANK by default. This is usually what you want for a ratio. There is no margin to compute if there is no revenue.

We covered this in more detail in a separate post about DIVIDE versus the slash operator, but the principle is the same. Default to BLANK behaviour. Override only when you have a specific business reason.

What this looks like in a visual

A common objection from analysts and stakeholders is that BLANK cells look unprofessional. The chart has gaps. The table has empty rows.

The answer is to configure the visual, not to mangle the measure. Power BI has several relevant settings:

  • Tables and matrices can be set to show or hide rows where all measures are BLANK
  • Line charts can be configured to skip gaps or render zero
  • Card visuals can show a custom text like "No data" when the measure returns BLANK
  • Conditional formatting can highlight blanks if that visibility is wanted

All of these are display concerns. The measure should carry honest data. The visual should present it appropriately. Mixing these two layers is what gets you into trouble.

If you have stakeholders who insist on never seeing BLANK in a report, work with them on the visual configuration rather than the measure logic. It will save you from the kind of bug I described at the start of this post.

The auditing exercise

If you have inherited a Power BI model and you want to check whether it has this problem, here is what we usually do.

Open the model. Search the DAX measures for ISBLANK and IFERROR and IF patterns where the false branch returns a literal zero. Each instance is a candidate for review. Some will be justified. Many will not be.

For each one, ask two questions. What does the metric represent? What does BLANK mean in that context? If the answers do not justify the conversion, change the measure.

This is the kind of exercise that takes a couple of hours on a medium-sized model and tends to surface real issues. We do this as a standard part of any Power BI health check we run through our business intelligence solutions practice.

The broader point

DAX is precise about what BLANK means and how it propagates. The semantics are deliberate. When you override them with hand-written IF statements, you are usually trading correctness for cosmetic improvements. That trade rarely works out well over the lifetime of a model.

The good Power BI models I have seen treat BLANK as information, not as a problem to hide. The bad ones treat every empty cell as something to paper over. The difference shows up two years later, when somebody asks a question the model cannot honestly answer because too much nuance has been smoothed out of the data.

If you are building or rebuilding a Power BI model and want to get the foundations right, our team works on these engagements regularly. Have a look at the Power BI consultants page or the broader Microsoft AI consulting services we offer.

Reference: Microsoft's DAX best practice on avoiding BLANK conversions is the official guidance.