Power BI Show Items With No Data: How It Works and How to Show 0

A regional manager once looked at a report I’d built and asked, quite reasonably, where the North region had gone. The table of sales by region listed nine regions, but the company had twelve.

Nothing was broken: North, along with two other regions, simply hadn’t sold anything that quarter, and Power BI leaves out groups that have no data. For the manager, though, a missing row looked like a mistake, and a row showing zero would have told the real story.

You’ll see the same thing on charts, where months without orders quietly disappear from the axis. Power BI has a built-in option for exactly this situation, called Show items with no data, but it has a few quirks that are worth understanding before you rely on it.

Quick answer: Select the visual, go to the Build visual tab, open the dropdown arrow next to the grouping field (in Rows, Columns, X-axis, Legend or Category) and select Show items with no data. The missing rows come back with a blank value. To show 0 instead of blank, use a measure such as COALESCE(SUM(Sales[Quantity]), 0).

In this tutorial, I’ll show you how the option works, how to show every month on a chart, how to display 0 instead of blank, and what to check when the option seems to have no effect.

What does Show items with no data do?

When Power BI builds a visual, it calculates your measure for every group, such as every region or month, and then drops the groups where the result is blank. Show items with no data tells it to keep those groups, so every region, product or month appears, including the ones with nothing to show. Microsoft explains the mechanics in Show items with no data in Power BI.

Two details catch people out the first time they use it. First, the rows it adds show a blank value, not 0, so you may still need a small change to your measure (I’ll cover that below). Second, it works like a left outer join between the grouping fields, which means the order of the fields changes the result. Put the field whose values you want to see in full first.

How to turn on Show items with no data

To show how it works, I’ll use a small table of sales quantity by region, where four regions have no quantity at all:

When I add the region name and quantity to a table visual, Power BI only lists the regions that had sales. East Midlands, North and South East are nowhere to be seen:

To bring them back, select the visual and open the Build visual tab in the Visualizations pane. In the field well, find the grouping field, which is Region name here. Select the dropdown arrow next to the field name, or right-click it, and select Show items with no data.

Every region now appears in the table, and the ones without sales have a blank quantity:

If you can’t find the option, check which field you’re looking at. It only appears on grouping fields: Rows or Columns in a matrix, Columns in a table, and X-axis, Legend or Category in charts. It isn’t available on fields you use as values.

How to show 0 instead of blank

Show items with no data brings the rows back, but they stay blank, and for many readers a blank cell is less clear than a zero. To display 0, change the measure rather than the visual. The COALESCE() function returns the first value that isn’t blank, so you can give it a fallback of 0:

Total Quantity = COALESCE ( SUM ( Sales[Quantity] ), 0 )

Adding + 0 to a measure has the same effect, and you’ll often see it in older reports:

Order Count = COUNT ( Sales[OrderID] ) + 0

There’s an important side effect, though. Once a measure turns blank into 0, it’s never blank, so Power BI shows every combination of the grouping fields, even with Show items with no data turned off. On large tables, that can produce thousands of rows and slow the report down noticeably. Microsoft’s DAX guidance recommends returning blank where there’s no meaningful value, and using Show items with no data only where you need all the groups.

Used carefully, though, it works well. In this example, a separate table of names is related to the sales table, and the matrix uses a measure that returns 0 when there are no records:

Every name now appears, with 0 where there’s nothing to report:

For more ways to do this in a matrix, see how to replace blank values with zero in a Power BI matrix.

Show all months on a chart, even months without data

Charts are where missing data is easiest to miss. On a column or bar chart with a date hierarchy, months without sales simply disappear from the axis, so the chart looks continuous even though it isn’t. In this chart of sales by month, several months are missing, and it’s hard to tell at a glance:

To fix it, select the chart, open the dropdown next to the date field in X-axis, and select Show items with no data. Every month in the date range now appears, and the gaps are obvious:

For results that stay reliable across years, use a proper date table marked as a date table, and put its Month column on the axis rather than the date from your sales table. See how to create a date table in Power BI if you don’t have one yet.

Line charts behave a little differently. With a continuous X-axis, the line simply joins the points on either side of a gap, so missing months don’t stand out. If you want each missing month shown, set the X-axis type to Categorical, and use a measure that returns 0 if you want the line to drop to zero.

Why Show items with no data isn’t working

If you turn the option on and nothing changes, the cause is usually in the model rather than the visual. Microsoft lists four cases where the option has no effect:

• There’s no measure in the visual, and all grouping fields come from the same table.
• The groups aren’t related in the model, because there’s no relationship path between their tables.
• The measure isn’t related to any of the groups, so it returns the same value for every combination.
• A measure filter excludes blanks, for example a visual filter such as Sales Amount is greater than 0.

If none of those apply, there are a few more things I check. Show items with no data isn’t available at all when the visual uses visual calculations.

Because of the left-outer-join behaviour, the order of the fields matters, so try swapping the grouping fields in the well. Make sure you turned the option on for the grouping field, not the value. And confirm that the table of the field you want to see in full is related to the table the measure uses.

Things to know before you turn it on

The option applies to the whole field well, not just one field. Turning it on for one field turns it on for every field in the same well, for example all the Rows fields of a matrix, although fields in other wells aren’t affected.

It also affects exports. Export summarized data treats the visual like a table, so the option applies to all the exported fields, and the file can contain far more rows than the visual shows.

Finally, Microsoft warns that it can make visuals slower and cause exports to time out on large models. I only turn it on where the empty groups actually matter to the reader, such as a list of regions or a monthly trend.

You may also find these Power BI guides useful:

• Replace Blank Values with Zero in Power BI Matrix Visual
• How to Show All Values in Power BI Matrix [Not Just First Value]
• How to Turn Off Grand Totals in Power BI Matrix [Row & Column]
• Power BI Date Table: 5 Methods + DAX Code [Complete Guide]
• Power bi show value as percentage + 13 Examples
• How to Remove Blank From Slicer in Power BI?

Frequently Asked Questions