Forum Discussion
Using PATH(), How Do I Remove Blanks From My Hierarchy Between Levels?
Hello awesome communty!
I'm learning the PATH() function, what I'm doing is, I want to create a product category hierarchy, I'm getting a table from Dynamics 365 F&O called "RetailProductHierarchyCategories" it has a "CategoryName" field and "ParentCategoryName" field.
I want to create a hierarchy for this so I made a new column called it PATH and it has this value:
Path = PATH(RetailProductHierarchyCategories[CategoryName];RetailProductHierarchyCategories[ParentCategoryName])
And then, since the hierarchy in my D365 F&O has 6 Levels, so I created 6 new columns named CategoryL1,2,3...6 and their values are as follows:
CategoryL1 = PATHITEM(RetailProductHierarchyCategories[Path];1) CategoryL2 = PATHITEM(RetailProductHierarchyCategories[Path];2) .... CategoryL6 = PATHITEM(RetailProductHierarchyCategories[Path];6)
After that, I created a new hierarchy column and dragged the 6 columns in this new hierarchy column.
And here is the result I got:
I got the hierarchy right except it includes blanks for each level till the 6th level.
For example, some categories has only 3 levels, but it includes blanks to each level till the 6th level.
How do I make PATH() to stop at the last possible level, I mean if this category has 4 levels, I want it to stop at level 4 and shows those 4 levels only and not go deeper with blanks?
The hierarchy slicer has a toggle called "Empty Leaves" but it does not filter the blanks on other visual types.
How can I resolve this issue? or what I've done wrong?
Thanks in advance! :)
12 Replies
I believe that PATH returns a string with hierarchy separated by | so perhaps replace || with BLANK().
Hello Mr. Creg,
The Path column displays fine, there are no empty spaces in the path string.
Thanks.
Hi I_NeedMorePower,
Please provide some sample data to help clear your table structure.
Regards,
Xiaoxin ShengHere is an example:
RetailProductHierarchyCategories Table:
CategoryName ParentCategoryName
TopCategory NULL
Cakes TopCategory
Drinks TopCategory
Soft Drinks Drinks
Cola Soft Drinks
Hot Drinks Drinks
Tea Hot Drinks
-------------------------------------------
The previous table has 4 levels of categories: TopCategory > Drinks > Soft Drinks > Cola.
After applying the steps I descriped in the main post, making a PATH column and creating 4 columns for each level and applying the PATHITEM() function for each column, the previous table with resulted collumns will look like this:
CategoryName ParentCategoryName CategoryL1 CategoryL2 CategoryL3 CategoryL4
TopCategory NULL TopCategory
Blank Blank BlankCakes TopCategory TopCategory Cakes
Blank BlankDrinks TopCategory TopCategory Drinks
Blank BlankSoftDrinks Drinks TopCategory Drinks SoftDrinks
BlankCola SoftDrinks TopCategory Drinks SoftDrinks Cola
HotDrinks Drinks TopCategory Drinks HotDrinks
BlankTea HotDrinks TopCategory Drinks HotDrinks Tea
-------------------------------------------------------------------------
The result hierarchy will look like this:
>TopCategory
...............................................................................................
As you can see there are blanks between each level till the 4th level. I believe it's because it reads the blanks in the table.
I hope I calrified the things with this humble sketch haha.
Here is an excel screenshot for the table I used in the example in my previous reply for a clear look:
HI I_NeedMorePower,
I'd like to suggest you replace the first level parent field as itself value, current path function seems not works if parameter fields has null value.
Regards,
Xiaoxin Sheng
