Forum Discussion

Regular Visitor

10 months ago

Solved

Power BI Hierarchy Using PATH Function Shows Blank Nodes

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:
How can I create a fully clean, expandable hierarchy from Level 1 → Level 5 in Power BI using RECID?
Is it better to do this in Power Query or DAX?
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!

Need Help

Royel
10 months ago
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
10 months ago
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.

12 Replies

Royel

Super User

10 months ago

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
Regular Visitor
10 months ago
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
  Regular Visitor
  10 months ago
  Royel Thankyou for your reply
  Here are some of my values for Name,ParentCategory,RecID values

Royel
Super User
10 months ago
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
  Regular Visitor
  10 months ago
  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
    Community Support
    10 months ago
    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.
    
    EcoResCategory.pbix27 KB
v-karpurapud
Community Support
10 months ago
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.
v-karpurapud
Community Support
10 months ago
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
Community Support
10 months ago
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.

Forum Discussion

Power BI Hierarchy Using PATH Function Shows Blank Nodes

12 Replies

Related content

AI functionality using Power BI Dataset

Date hierarchy

Hierarchy Slicer Metadata for Auto Date/Time Hierarchies in Power BI Embedded

Regular Expression Functions (REGEX) in Power Query

Calender Function Hierarchy Visual