How to Show Items with No Data in Power BI

It can be puzzling when your Power BI chart of monthly sales only shows March, April, and June, completely skipping the months with zero revenue. Power BI is designed to be efficient by default, which means it often hides categories that have no data to report. This article will show you exactly how to override that behavior and display all the items you need to see, giving you a complete and accurate picture of your performance.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Why Does Power BI Hide Items with No Data?

By default, Power BI automatically filters out data points where the measure is blank. Let's say you're tracking sales for a new product line. Your sales table might look like this:

• January: $0 Sales
• February: $0 Sales
• March: $5,000 Sales
• April: $8,000 Sales
• May: $0 Sales

If you build a standard bar chart in Power BI with "Month" on the axis and "Sales" as the value, Power BI will only display bars for March and April. From a data processing perspective, it sees no value associated with January, February, or May, so it excludes them to create a cleaner, more focused visual. While this is helpful in some cases, it can be misleading when you need to see a continuous timeline or report on which salespeople haven't made a sale. Hiding these "zeroes" masks important context - like the fact that sales took two months to get off the ground and dipped again in May.

Using the "Show Items with No Data" Feature

The simplest way to fix this is by using a built-in Power BI feature specifically for this situation. It tells a visual to show every possible value from a field, regardless of whether it has a corresponding measure.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step-by-Step Instructions

Let’s walk through how to enable this setting using a sales-by-product example.

1. Create your visual: Start by adding a visual to your report canvas, like a Table, Matrix, or Bar Chart. For this example, let's use a Table.
2. Add your fields: Drag the categorical field you want to see all values for into the visual. For instance, drag ProductName from your Products dimension table into the "Rows" or "Columns" bucket.
3. Add your measure: Now, drag your numerical measure, like Total Sales from your Sales fact table, into the "Values" bucket. At this point, you'll likely notice that some products are missing - the ones that have never been sold.
4. Activate the feature: In the visualization pane where you dragged your fields, find the categorical field you want to adjust (in this case, ProductName).
5. Click the small down-arrow next to the field's name to open its context menu.
6. Select "Show items with no data."

Instantly, your table will update. It will now list every single product from your Products table, showing a blank value or a zero for those products that have no sales data. This makes it easy to see your complete product catalog and identify the non-sellers at a glance.

This feature works on the axes of charts, too. If your monthly sales chart is missing months, you would apply "Show items with no data" to your 'Month' field on the X-axis.

Troubleshooting: Why is "Show Items with No Data" Greyed Out?

Sometimes, you'll go to select the option, only to find it's greyed out and unavailable. This is a common snag that usually boils down to a few key reasons related to your data model and visual setup.

Reason 1: The Field Is from the Wrong Table

This is the most frequent cause. The "Show items with no data" feature is designed to work on fields from the "one" side of a one-to-many relationship. In a standard data model, you have dimension tables (like 'Products', 'Customers', or 'Calendar') that describe your business entities, and fact tables (like 'Sales' or 'Traffic') that contain numerical measurements.

• Correct Setup: Your model has a 'Calendar' table with every date, and your 'Sales' records are linked to it. You use the 'Month' field from your Calendar table on your chart's axis. This works because the Calendar table contains all months, independent of sales.
• Incorrect Setup: You're using the 'OrderDate' field directly from your Sales table. Because the Sales table only contains dates when a sale occurred, Power BI doesn't know about months with no sales. The feature won't work because there are no "items with no data" to show - the items literally don't exist in that column.

The Fix: Always build visuals using columns from your dimension tables (Products, Calendar, SalesRep, etc.) for axes and categories, and measures from your fact tables for values.

Reason 2: The Visual Lacks a Measure

The feature needs a measure to determine which items lack data. If you only have a categorical field in your visual (like a table with just a list of Product Names), Power BI has no numerical context to check against. You must have at least one field in the "Values" target of the visualization pane for the option to become active.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Reason 3: Your Data Source Has No Relationship

For Power BI to understand how to find items with no data, the tables must be properly related in the Model view. An unrelated table provides no path for Power BI to determine which products have sales and which ones do not.

A More Powerful Solution: Using DAX to Manage Blanks

While the "Show items with no data" feature is a great quick fix, it's not always the most flexible or performant solution. A more robust approach is to write a Data Analysis Expressions (DAX) measure that explicitly converts blank results into zeros. When a category has a 0 instead of a BLANK(), Power BI sees it as an actual value and automatically includes it in the visual without needing any special settings.

The Quick and Easy "+ 0" Trick

The simplest way to turn blanks into zeros is to add zero to your existing measure. Create a new measure with a formula like this:

Total Sales (with Zeroes) = SUM('Sales'[Revenue]) + 0

In DAX, any mathematical operation involving a BLANK() value results in BLANK(), except when you add zero. BLANK() + 0 evaluates to 0. By using this measure instead of your base measure, any product, month, or region with no sales will now be assigned a value of 0 and will appear in your visual by default.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

The Airtight Method Using COALESCE

The + 0 trick is handy, but it can sometimes have unintended side effects in more complex DAX calculations. A more explicit and reliable method is to use the COALESCE function. COALESCE evaluates a list of expressions and returns the first one that is not blank.

Here’s how you would write the measure:

Total Sales (Coalesce) = COALESCE(SUM('Sales'[Revenue]), 0)

This formula checks the SUM('Sales'[Revenue]). If it returns a value (e.g., $1,500), COALESCE passes that value through. If the sum is BLANK(), the function skips it and returns the next value in the list, which is 0. This is considered a best practice for DAX developers as it clearly states your intention and is very easy to read.

An older but similar approach uses an IF statement, which also works perfectly but is slightly more verbose:

Total Sales (IF) = IF(ISBLANK(SUM('Sales'[Revenue])), 0, SUM('Sales'[Revenue]))

This formula simply says: "If the sum of revenue is blank, return 0, otherwise, return the sum of revenue."

Best Practices for a Complete Picture

To avoid these issues in the future and build more resilient reports, keep a couple of best practices in mind:

1. Always Use a Calendar Table: The foundation of any good time intelligence analysis is a dedicated calendar table. It should contain a continuous list of dates, from the start to the end of your desired reporting period, with columns for month, quarter, year, etc. This ensures you always have a complete timeline to report against.
2. Build a Proper Star Schema: Separate your descriptive data (customers, products) from your measurement data (sales, ad spend). Connect them in the Model view with one-to-many relationships. This not only solves the "missing items" problem but dramatically improves report performance and simplicity.
3. Favor Explicit DAX Measures: Instead of relying on Power BI's built-in sum/count summarizations, get into the habit of writing explicit DAX measures like Total Sales = SUM(Sales[Revenue]). This gives you a central point of logic to control formatting, handle blanks, and build more complex calculations later on.

Final Thoughts

Ensuring your reports tell the full story by showing items with no data is a crucial step in building trust and clarity. You can either use Power BI's convenient "Show items with no data" feature for a quick adjustment or take control with a more robust DAX measure that converts blank values into zeros.

We know that mastering data modeling rules, navigating interface quirks, and writing DAX can feel like a full-time job. We ran into these hurdles ourselves when trying to get simple answers from our business data, which is why we built Graphed. It's an AI data analyst that lets you connect your data sources - like Shopify, Google Analytics, and Hubspot - and create real-time dashboards just by describing what you want to see. Instead of hunting down settings or learning formulas, you can focus on the questions you need answered.