How to Show All Rows in Matrix Power BI

A Power BI matrix can sometimes feel like it has a mind of its own, hiding rows that contain no data. This is actually a feature designed to keep your reports tidy, but it can be frustrating when you need to see a complete picture. This article will walk you through exactly how to force your Power BI matrix to show all rows, including those without corresponding values.

Why Power BI Hides Rows in the First Place

Before jumping into the solution, it’s helpful to understand the "why." By default, Power BI operates on efficiency. If you're building a matrix of product sales by month, and a certain product had zero sales in April, Power BI assumes you don't want to see a blank row for that product in that month. It automatically filters out combinations of rows and columns that don't have any data in the "Values" field.

This is often useful because it prevents your reports from becoming cluttered with empty cells. Imagine a report with a thousand products and twelve months - displaying every single combination would create a massive, mostly empty visual. However, for many types of analysis, seeing these gaps is exactly the point. You might want to know which products aren't selling or which sales reps haven't closed a deal this quarter.

Luckily, overriding this default behavior is straightforward once you know where to look.

The Easiest Method: "Show Items with No Data"

The quickest way to solve this problem is by using a built-in feature designed for this exact purpose. It tells Power BI to display all possible items from a field, regardless of whether they have a matching value.

Here’s the step-by-step process:

1. Select Your Matrix Visual: Click on the matrix in your report canvas to activate it.
2. Go to the Fields Pane: The Fields pane is typically located on the right-hand side of the Power BI window. Here you will see the wells for Rows, Columns, and Values that make up your matrix.
3. Find the Field in the "Rows" Well: Identify the specific field in the "Rows" well for which you want to display all items. For example, if your matrix shows sales by Product Name, you'd be looking for the Product Name field.
4. Right-Click and Select the Option: Right-click on that field inside the "Rows" well. A context menu will appear. From this menu, select "Show items with no data."

As soon as you click it, Power BI will refresh the matrix, and you’ll see the previously hidden rows appear, usually with a blank value. This same technique also works for fields you've placed in the "Columns" well.

An Example in Action

Let's say you have a matrix showing Sales Amount by Sales Rep. You have five reps on your team: Alice, Bob, Carol, David, and Eve. In the last month, only Alice, Bob, and David made sales.

• Before: Your matrix only shows rows for Alice, Bob, and David. Carol and Eve are missing.
• After: After right-clicking the Sales Rep field in the Rows well and selecting "Show items with no data," your matrix will now display all five reps. The rows for Carol and Eve will be visible, with a blank entry for Sales Amount.

This simple trick solves the problem about 80% of the time.

When "Show Items with No Data" Isn't Enough

Occasionally, you'll follow the steps above and... nothing happens. This is a common point of frustration for Power BI users. The issue usually isn’t with the setting itself, but with the underlying data model. The "Show items with no data" feature can only show items that Power BI knows exists within the context of your data model.

This happens most often when you pull fields directly from a single "fact table" — a large table that contains transactional data like sales, leads, or web traffic.

Here's the problem: If your sales table only contains records of sales that happened, it has no record of products that didn't sell. If a product named "Super Widget" has never sold, it simply doesn't exist in your sales table. Therefore, Power BI has no way of knowing a "Super Widget" is a possible product, and the "Show items with no data" feature can't display it.

The solution is to use a proper data model with dimension tables.

• Fact Tables contain what happened data (e.g., Sales Orders, with columns for Date, ProductID, and Amount).
• Dimension Tables contain descriptive "who, what, where, when" data (e.g., a Products table with a complete list of every product you sell, or a Calendar table with every single date in a year).

When you create a relationship between your Products dimension table and your Sales fact table, you're telling Power BI, "Here's the complete, official list of all my products." Now, when you use the Product Name field from the Products table in your matrix, the "Show items with no data" feature works perfectly because it references the complete list, not just the products that had a sale.

Creating a Solid Data Model: A Practical Guide

Ensuring your model is set up correctly not only fixes the "missing rows" problem but also makes your reports faster and more reliable. The most important dimension table for almost any report is a calendar table.

1. Create a Dedicated Calendar Table

Instead of using date fields directly from your sales or marketing tables, it's a Power BI best practice to create a separate Calendar table. This gives you a continuous and complete list of dates to work with.

You can create one easily using a bit of DAX (Data Analysis Expressions):

1. Navigate to the "Data view" in Power BI.
2. From the "Home" or "Table tools" ribbon, click on "New Table."
3. Enter the following DAX formula and press Enter:

Calendar = 
CALENDARAUTO()

This powerful function scans your entire data model for the earliest and latest dates and creates a single-column table named "Calendar" with a complete list of all dates in between. You can then add more columns for Month, Year, Quarter, etc., to make slicing and dicing your data even easier.

2. Build the Relationship

Once you have your Calendar table and your Products table, go to the "Model view." Drag the Date field from your new Calendar table onto the corresponding date field in your sales table. Do the same by dragging the ProductID from your Products table to the ProductID in the sales table. This creates the relationships that Power BI needs to understand the full context of your data.

With this structure in place, build your matrix by dragging Product Name (from your Products table) to Rows and Month (from your Calendar table) to Columns. Now, "Show items with no data" will work reliably.

Displaying Zeros Instead of Blanks

After you get your hidden rows to show up, you'll notice Power BI leaves the value cells blank. This is technically accurate, as a lack of data isn't the same as a value of zero. However, for readability, you might want to display a 0 instead.

This is easily done by modifying your DAX measure slightly. If you're using a simple SUM measure like:

Total Sales = SUM(Sales[SalesAmount])

You can force a zero to appear by simply adding + 0 to the end of the expression:

Total Sales with Zeros = SUM(Sales[SalesAmount]) + 0

When you use this new measure in your matrix, any row that would have been blank will now display a 0. It's a small change, but it makes your reports look much cleaner and more professional.

Final Thoughts

Getting your Power BI matrix to show all rows often comes down to two key steps: using the "Show items with no data" option on the correct field and ensuring your report is built on a solid data model with proper dimension tables. While it might seem like a small detail, displaying these empty rows is often critical for uncovering the full story behind your data.

We know that getting these data models and DAX measures just right can be a time-consuming part of analytics. You often spend more time wrangling data in tools like Power BI than you do acting on the insights. That's why we created Graphed. Instead of manually building relationships and writing formulas, you can connect your data sources in seconds and simply ask, "Show me a chart of sales for all products by month," and our AI handles building the visual for you, correctly, in real time. It's designed to bring you straight to the insights, skipping the setup complexities.