How to Remove Blanks from Hierarchy in Power BI

Nothing brings a Power BI report to a screeching halt quite like unwanted blank rows cluttering up your hierarchy. You’ve built the perfect matrix visual, only to see entire sections of empty space because some categories don't have sub-categories. This article will show you several ways to clean up those visuals and remove the blanks for good.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

First, Why Do Blank Rows Appear in Hierarchies?

This issue usually pops up because you're working with a "ragged" or "unbalanced" hierarchy. This just means your data structure isn't perfectly symmetrical. Some members have children, and some don’t. Think of a simple Region > Country > City hierarchy:

• North America (Region) has USA (Country), which has New York (City).
• Europe (Region) has Vatican City (Country), which has no further city breakdown in your data. It's its own lowest level.

When you put this into a Power BI matrix or table, the software tries to create a uniform structure. For Vatican City, it will show the Region and Country, but since there's no City, it leaves a (Blank) placeholder at that level. This creates visual noise and can confuse anyone trying to read your report. Now, let's get into how to fix it.

Method 1: The Quickest Fix for Matrix Visuals

Sometimes Power BI provides a simple, one-click solution, but it only works for specific visuals like the Matrix. If you have blanks appearing specifically because a level in the hierarchy has no measure value associated with it, this is for you.

For this to work, you generally need to put the matrix into its stepped layout.

1. Select your matrix visual.
2. Go to the Format your visual pane (the paintbrush icon).
3. Expand the Row headers section.
4. Under Options, slide the toggle for Stepped layout to On. The "+/- icons" option should now appear below it.

Next, find the option to hide items with no data.

1. Navigate to the relevant field in the Fields pane.
2. Click the dropdown arrow next to the field name in the visual's field well (e.g., in the "Rows" area).
3. Select Show items with no data to toggle it off if it is selected.

When to use this: This is a great first attempt for matrix visuals, especially if your blanks are due to missing measure values rather than structural hierarchy gaps. If it doesn't solve your problem, it's time to move to a more direct filtering approach.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Method 2: Use the Filters Pane to Hide Blanks

The most direct way to remove blanks is to simply filter them out. This method is straightforward and can be applied at the visual, page, or even the entire report level.

The key here is to filter based on the lowest level of your hierarchy. If your hierarchy is Category > Sub-Category > Product, then a blank will appear when a Sub-Category has no Products associated with it. To fix this, you filter out where 'Product' is blank.

Step-by-Step Instructions:

1. Select the visual you want to clean up.
2. Make sure the Filters pane is visible. If not, go to the View tab and check the box for "Filters."
3. Find the field that represents the lowest level of your hierarchy (e.g., 'Product'). Drag it from the Fields pane into the "Filters on this visual" area within the Filters pane.
4. A new filter card will appear. Click the dropdown for Filter type and choose Advanced filtering.
5. For the "Show items when the value" condition, select is not blank from the dropdown menu.
6. Click Apply filter.

The blank rows corresponding to that field should instantly disappear from your visual. You’ve told Power BI to only show rows where the lowest-level item actually exists.

Trade-offs: This works perfectly most of the time. However, be aware that if a parent item's only children are blanks, and you want that parent item to still show up with a value of 0, this method will remove the parent item entirely. If you run into that, a DAX-based solution offers more control.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Method 3: Build a Powerful DAX Measure to Control Blanks

For ultimate control and flexibility, you can create a simple DAX (Data Analysis Expressions) measure. This might sound intimidating, but it's often a copy-and-paste formula that feels like writing an Excel function. This approach gives you a reusable tool to control exactly what gets shown.

The goal is to create a "flagging" measure that returns 1 (or true) if the relevant row has data, and 0 (or false/blank) if it doesn't. Then, we can use that measure to filter the visual.

Step 1: Check if Your Core Measure Has a Value

First, we need a core measure that you're analyzing, like Total Sales. If you don't already have one, create it. Right-click your table in the Fields list and select "New measure".

Total Sales = SUM(Sales[Revenue])

Step 2: Create a “Validator” Measure

Next, we’ll create a helper measure. This measure will check if there’s a value for Total Sales or if a key identifier (like Product Key) exists for the current row in the hierarchy. It returns 1 if data is present, otherwise, it returns a blank.

1. Right-click your measures table and select New Measure.
2. Enter the following DAX formula. Remember to replace 'SalesTable'[Product Key] with the lowest level of your hierarchy and [Total Sales] with your main measure.

IsValidRow = 
IF(
    NOT(ISBLANK([Total Sales])), 
    1, 
    IF(
        HASONEVALUE('SalesTable'[Product Key]),
        1,
        BLANK()
    )
)

This formula checks two things: "Is there a sales value?" If so, return 1. If not, "Is this a row representing a single product?" If so, return 1. Otherwise, return nothing (BLANK).

Step 3: Apply the Measure as a Visual Filter

Now, bring this new measure into your Filters pane:

1. Select your visual.
2. Drag your new [IsValidRow] measure into the Filters on this visual section.
3. Set the filter condition to is not blank.
4. Click Apply filter.

Your visual will refresh, and because the blank rows couldn't produce a value of '1' from your measure, they will be hidden from view. This approach is more robust because it's based on the logic you define and can be easily applied to many visuals.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Method 4: The Best Long-Term Solution: Clean Your Data in Power Query

All the methods above are great for fixing visuals in your report. But a more permanent solution is to address the issue at its source: in your data model. Power Query (the data transformation engine inside Power BI) is the perfect tool for this.

By cleaning the data before it even hits your canvas, every visual you build will be cleaner by default.

Filling Down or Replacing Blanks

Let's say your hierarchy issue stems from rows where the Sub-Category is null or blank.

1. From the Home tab ribbon in Power BI Desktop, click Transform data to open the Power Query Editor.
2. In the left pane, select the query (table) that contains your hierarchy.
3. Find the column with the blank values (for instance, Sub-Category).
4. Right-click the column header.
5. You have a few options:
6. Once you've made your changes, click Close & Apply in the top-left corner.

Fixing the issue in Power Query means your DAX can be simpler, your reports will be more performant, and you won’t have to apply the same visual-level filters over and over again.

Final Thoughts

Dealing with blank values in Power BI hierarchies is a common hurdle, but it's easily managed. You can use quick visual settings, apply filters directly, write a flexible DAX measure for more control, or fix the data upstream in Power Query for a truly robust solution. Choosing the right method depends on whether you need a quick fix or a permanent data model improvement.

We know how frustrating it can be to get your data just right in tools like Power BI. That's why we created Graphed. Instead of navigating endless menus and debugging DAX formulas, you can connect your data sources in seconds and create dashboards simply by asking questions in plain English - like "show me sales by category and subcategory from Shopify." Graphed automates the manual busywork, so you can skip right to the insights.