Reply
Frequent Visitor
Posts: 15
Registered: ‎01-30-2019

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:

Capture.PNG

 

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! Smiley Happy

Highlighted
Super User
Posts: 10,605
Registered: ‎07-11-2015

Re: Using PATH(), How Do I Remove Blanks From My Hierarchy Between Levels?

I believe that PATH returns a string with hierarchy separated by | so perhaps replace || with BLANK().


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Community Support Team
Posts: 7,505
Registered: ‎08-14-2016

Re: Using PATH(), How Do I Remove Blanks From My Hierarchy Between Levels?

Hi @I_NeedMorePower,

 

Please provide some sample data to help clear your table structure.

 

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
Frequent Visitor
Posts: 15
Registered: ‎01-30-2019

Re: Using PATH(), How Do I Remove Blanks From My Hierarchy Between Levels?

[ Edited ]

Here 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                   Blank

Cakes                        TopCategory                       TopCategory       Cakes                            Blank                   Blank

Drinks                       TopCategory                       TopCategory       Drinks                           Blank                   Blank

SoftDrinks                 Drinks                                 TopCategory       Drinks                 SoftDrinks                     Blank

Cola                           SoftDrinks                          TopCategory       Drinks                 SoftDrinks          Cola

HotDrinks                  Drinks                                TopCategory       Drinks                 HotDrinks                      Blank

Tea                             HotDrinks                          TopCategory       Drinks                 HotDrinks           Tea

-------------------------------------------------------------------------

The result hierarchy will look like this:

 

>TopCategory

        >Blank

                >Blank

                        >Blank

        >Cakes

                >Blank

                        >Blank

        >Drinks

                >Blank

                        >Blank

                >Soft Drinks

                        > Blank

                        > Cola

                >Hot Drinks

                        >Blank

                        > Tea

...............................................................................................

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.

 

Frequent Visitor
Posts: 15
Registered: ‎01-30-2019

Re: Using PATH(), How Do I Remove Blanks From My Hierarchy Between Levels?

Hello Mr. Creg,

The Path column displays fine, there are no empty spaces in the path string.

 

Thanks.

Frequent Visitor
Posts: 15
Registered: ‎01-30-2019

Re: Using PATH(), How Do I Remove Blanks From My Hierarchy Between Levels?

Here is an excel screenshot for the table I used in the example in my previous reply for a clear look:

Capture.PNG

 

Community Support Team
Posts: 7,505
Registered: ‎08-14-2016

Re: Using PATH(), How Do I Remove Blanks From My Hierarchy Between Levels?

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.

9.png

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
Frequent Visitor
Posts: 15
Registered: ‎01-30-2019

Re: Using PATH(), How Do I Remove Blanks From My Hierarchy Between Levels?

[ Edited ]

Hello there!

 

I tried what you suggested to me, to make the parent of the root the same name instead of null, but it gave me the same result.

 

It seems if I wanted to remove the blank leaves is only by using the heirarchy slicer with turning off the "empty leaves" option.

 

Thank you for your time to help Smiley Happy

Frequent Visitor
Posts: 15
Registered: ‎01-30-2019

Re: Using PATH(), How Do I Remove Blanks From My Hierarchy Between Levels?

[ Edited ]

I found this blog explaining the workaround for the blanks in creating a heirarchy path:

https://www.wiseowl.co.uk/blog/s2479/parent-child-hierarchy.htm

 

The writer's work around is simply making the categories levels function to check if it is blank? then take the same value of the previous level.

 

here is the DAX function:

CategoryL6 = IF (

ISBLANK(PATHITEM ( [CategoryRecordId]; 6; INTEGER )) ;

RetailProductHierarchyCategories[CategoryL5];

LOOKUPVALUE (

[CategoryName];

[CategoryRecordId]; PATHITEM ( [Path]; 6; INTEGER )

)
)

What this DAX function does is, it checks if this current level (in this DAX example Level 6) is blank....so if it's blank, then take the same value of the previous level. So it should replace the blanks with the previous values ending up with no blanks.

But when I applied it on my columns. it does not replace the blanks. like it does nothing. I tried to play around the ISBLANK() part, then it turned out it does not see the blank fields as blank...so it takes the false part of the IF always.....

I don't know why.... 

what can I do to make the ISBLANK() returns true on the blank fields?

 

Thanks.