Power BI Hierarchy Using PATH Function Shows Blank Nodes

[sravanicse48]

Hello everyone,

I am trying to create a clean category hierarchy in Power BI using the PATH function from Level 1 to Level 5. My dataset has the following relevant columns:

• RECID → unique identifier for each category (used instead of PKWIUCODE)

• PARENTCATEGORY → parent category’s RECID (0 or null for root)

• NAME → category name

  I created the Path column like this:

   

  Path = PATH('EcoResCategory'[RECID], 'EcoResCategory'[PARENTCATEGORY])

  Then I created calculated columns for each level:

   

  Level1 = PATHITEM('EcoResCategory'[Path], 1, INTEGER) so on until level 4
  and then created 

  Level1Name = LOOKUPVALUE('EcoResCategory'[Name], 'EcoResCategory'[RECID], 'EcoResCategory'[Level1]) upto level 4

  And then I used LOOKUPVALUE to get the actual names:

   

  Level1Name = LOOKUPVALUE('EcoResCategory'[NAME], 'EcoResCategory'[RECID], [Level1])

  …and similarly for Level2 → Level5.

  Problem:

  When I use these columns in a hierarchy slicer, I see blank nodes at various levels, and sometimes expanding a parent shows the same subcategory multiple times.

  I have tried:

  • Using DISTINCT

  • Filtering NOT(ISBLANK(...))

  • Creating a separate hierarchy table using SUMMARIZE

    …but I still either get blank nodes or duplicate subcategories.

    Questions:

    1. How can I create a fully clean, expandable hierarchy from Level 1 → Level 5 in Power BI using RECID?

    2. Is it better to do this in Power Query or DAX?

    3. What is the recommended approach if some parent categories have no children at certain levels but I still want the parent visible in the slicer?

       Any guidance, examples, or best practices would be greatly appreciated!

[Royel · Accepted solution]

Hay @sravanicse48 thanks for share sample data. Since you also want to hide blank items. I tried with a different visual (Hierarchy Slicer) you can find it in app store. 

Configuration: 

Select the visual -> Selection Control -> Hide Member -> Empty 

Results: 

Left one is power bi default slicer and right one is from Hierarchy Slicer and as you can see its not showing the empty any more. 

File: https://drive.google.com/file/d/1n1uMcEb4fr6pzpvk3A7kQmzORWeLR0uo/view?usp=drive_link 

Thanks 

[v-karpurapud]

Hi @sravanicse48 

I wanted to check if you’ve had a chance to review the information provided. If you have any further questions, please let us know. Has your issue been resolved? If not, please share more details so we can assist you further.

Thank You.

[v-karpurapud]

Hi @sravanicse48 

We have not received a response from you regarding the query and were following up to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank You.

[v-karpurapud]

Hi @sravanicse48 

Thank you for submitting your question to the Microsoft Fabric Community Forum, and thanks to @Royel for offering helpful suggestions.

Could you let us know if the suggested solution resolved your issue?If you still need help, please share more details so we can assist you further.

Thank you.

[Royel]

As you can see when PARENTCATEGORY column is 0 it takes RECID values. In this case RECID should not be null. 

Seems like you have a different scinerio, would you please share the distinct values from RECID and PARENTCATEGORY column. It will help to adjust the logic. 

Thanks 

[sravanicse48]

@Royel Even in my case when Parentcategory is null it takes RECID  values

I followed the DAX approach to build a category hierarchy using the PATH function. The CleanParent column correctly replaces blanks or 0s in the PARENTCATEGORY column with the record’s own RECID, and the Path column is generating as expected.

However, I noticed that when Level1 is a root category (for example, Electronics), the Level2, Level3, and Level4 columns are blank. When this hierarchy is used in a slicer, these blanks show up as separate blank nodes, which makes the slicer look messy.

My question:
How can I prevent these blank nodes from appearing in the slicer while still maintaining the hierarchy structure for categories that have subcategories?

Sample data illustration:

• When PARENTCATEGORY = 0, the CleanParent takes the same RECID.

• For Electronics (RECID=1), Level2, Level3, Level4 are blank.

• These blanks are visible as empty nodes when the hierarchy is expanded in the slicer.

Expected behavior:
Root-level categories (like Electronics, Furniture, Appliances) should display normally, but the slicer should not show any blank nodes under them.

Any suggestions on how to modify the DAX or slicer setup to handle this?

[v-karpurapud]

Hi @sravanicse48 
Thank you for reaching out to the Microsoft Fabric Community Forum.

I have reproduced the scenario on my end using the sample category hierarchy data, and the solution is working as expected. Root categories such as Electronics, Furniture, and Appliances appear correctly at Level 1, while their subcategories expand down to Level 5 where applicable. To help you better understand the implementation, I have prepared and attached a sample .pbix file along with a snapshot that demonstrates the full hierarchy slicer setup. Please review the attached file at your convenience and share your observations so we can refine the solution further if needed.

 

I hope this information is helpful. . If you have any further questions, please let us know. we can assist you further.

Regards,

Microsoft Fabric Community Support Team.
 

[sravanicse48]

@v-karpurapud 

Thank you for sharing the sample .pbix file and the detailed explanation. I’ve reviewed it and I’m getting the same results as shown in your example. That’s actually what led me to reach out .I was wondering if there’s any way to avoid or hide the blank nodes in the slicer while still keeping the full hierarchy intact.

Could you please advise if there’s a workaround or DAX-based approach to remove those blank nodes?

Thanks again for your time and support!

[v-karpurapud · Accepted solution]

Hi @sravanicse48 

As @Royel mentioned, you may consider using the Hierarchy Slicer visual from the AppSource marketplace as an alternative workaround. This visual offers an extra configuration option to effectively handle blank members. After adding the Hierarchy Slicer to your report and enabling the empty setting, it will automatically remove any blank or empty nodes from the hierarchy, resulting in a clearer and more user-friendly slicer.

If you have any additional questions or require assistance with the configuration, please feel free to reach out.

Regards,

Microsoft Fabric Community Support Team.

[Royel]

Hi @sravanicse48  for better error handeling you need to create another column 

CleanParent = IF(
    ISBLANK(EcoResCategory[PARENTCATEGORY]) || EcoResCategory[PARENTCATEGORY] = 0, 
    EcoResCategory[RECID],           -- Self-reference for root nodes
    EcoResCategory[PARENTCATEGORY]   -- Keep original parent for child nodes
)

When it will find any null or blank or 0 it will call itself to overcome the error 

Here is the path column 

Path = 
VAR CleanParent = IF(ISBLANK(EcoResCategory[PARENTCATEGORY]) || EcoResCategory[PARENTCATEGORY] = 0, EcoResCategory[RECID], EcoResCategory[PARENTCATEGORY])
RETURN PATH(EcoResCategory[RECID], EcoResCategory[CleanParent])

and here are the levels column 

Level1 = 
VAR LevelNumber = 1
VAR LevelKey = PATHITEM(EcoResCategory[Path], LevelNumber, INTEGER)
VAR LevelName = IF(
    NOT(ISBLANK(LevelKey)),
    LOOKUPVALUE(EcoResCategory[NAME], EcoResCategory[RECID], LevelKey),
    BLANK()
)
RETURN LevelName

Level2 = 
VAR LevelNumber = 2
VAR LevelKey = PATHITEM(EcoResCategory[Path], LevelNumber, INTEGER)
VAR LevelName = IF(
    NOT(ISBLANK(LevelKey)),
    LOOKUPVALUE(EcoResCategory[NAME], EcoResCategory[RECID], LevelKey),
    BLANK()
)
VAR IsValidLevel = LevelName <> EcoResCategory[Level1] && NOT(ISBLANK(LevelName))
RETURN IF(IsValidLevel, LevelName, BLANK())


Level3 = 
VAR LevelNumber = 3
VAR LevelKey = PATHITEM(EcoResCategory[Path], LevelNumber, INTEGER)
VAR LevelName = IF(
    NOT(ISBLANK(LevelKey)),
    LOOKUPVALUE(EcoResCategory[NAME], EcoResCategory[RECID], LevelKey),
    BLANK()
)
VAR IsValidLevel = LevelName <> EcoResCategory[Level2] && 
                   LevelName <> EcoResCategory[Level1] && 
                   NOT(ISBLANK(LevelName))
RETURN IF(IsValidLevel, LevelName, BLANK())


Level4 = 
VAR LevelNumber = 4
VAR LevelKey = PATHITEM(EcoResCategory[Path], LevelNumber, INTEGER)
VAR LevelName = IF(
    NOT(ISBLANK(LevelKey)),
    LOOKUPVALUE(EcoResCategory[NAME], EcoResCategory[RECID], LevelKey),
    BLANK()
)
VAR IsValidLevel = LevelName <> EcoResCategory[Level3] && 
                   LevelName <> EcoResCategory[Level2] && 
                   LevelName <> EcoResCategory[Level1] && 
                   NOT(ISBLANK(LevelName))
RETURN IF(IsValidLevel, LevelName, BLANK())

You can continue based on your data.

Find this helpful? ✔ Give a Kudo • Mark as Solution – help others too!

[sravanicse48]

@Royel Hi Royel

Thank you for the suggestion! I implemented the CleanParent approach and created the Path column as well as the Level1 → Level4 calculated columns as you described.

However, I still see blank nodes appearing in my hierarchy when I use it in a slicer. Even with IsValidLevel checks, some parent nodes with missing children still show as blanks, which breaks the clean hierarchy I’m aiming for.

Has anyone successfully removed all blank nodes in a multi-level hierarchy using this method, or would creating a separate distinct hierarchy table in DAX / Power Query be a better approach?

[sravanicse48]

@Royel Thankyou for your reply
Here are some of my values for Name,ParentCategory,RecID values

[Royel · Accepted solution]

Hay @sravanicse48 thanks for share sample data. Since you also want to hide blank items. I tried with a different visual (Hierarchy Slicer) you can find it in app store. 

Configuration: 

Select the visual -> Selection Control -> Hide Member -> Empty 

Results: 

Left one is power bi default slicer and right one is from Hierarchy Slicer and as you can see its not showing the empty any more. 

File: https://drive.google.com/file/d/1n1uMcEb4fr6pzpvk3A7kQmzORWeLR0uo/view?usp=drive_link 

Thanks