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

Hi there lukaszcracow 

I have looked back at the file and here is what I ended up with:

For Path Field:

Path = PATH(RetailProductHierarchyCategories[CategoryName];RetailProductHierarchyCategories[ParentCategoryName])

and for Category Levels, here is the expression for each level field (Assuming for my case I have 6 Category Depth Levels):

CategoryL1 = PATHITEM(RetailProductHierarchyCategories[Path];1)
CategoryL2 = IF(PATHITEM(RetailProductHierarchyCategories[Path];2) = "";RetailProductHierarchyCategories[CategoryL1];PATHITEM(RetailProductHierarchyCategories[Path];2))
CategoryL3 = IF(PATHITEM(RetailProductHierarchyCategories[Path];3) = "";RetailProductHierarchyCategories[CategoryL2];PATHITEM(RetailProductHierarchyCategories[Path];3))
CategoryL4 = IF(PATHITEM(RetailProductHierarchyCategories[Path];4) = "";RetailProductHierarchyCategories[CategoryL3];PATHITEM(RetailProductHierarchyCategories[Path];4))
...
CategoryL6 = IF(PATHITEM(RetailProductHierarchyCategories[Path];6) = "";RetailProductHierarchyCategories[CategoryL5];PATHITEM(RetailProductHierarchyCategories[Path];6))

If you have less or more category levels, obviusly you have to remove or add more level fields.

What the Category Level fields that are after CategoryL1 do is, for each category level field:it looks into the path string and checks if the current level is available in the string or not.

For example:

Let's assume we have 6 category levels and the path string for one of the many categories is "Drinks | Cola"
the previous string has only 2 levels (Drinks and Cola), so what the CategoryL3,L4,L5,L6 do in the expressions I provided above is: it checks the path string (Drinks | Cola) for L3,L4,L5,L6 if it is "" (Blank).

Then If it is "" (Blank) it means it doesn't have a category for this level (L3,L4,L5,L6)..... so what I did as a workaround to remove the "Blanks" from the category trea is, I take the last Level name and duplicate it for the next empty levels.

following our example above "Drinks | Cola", the result will be:

Drinks > Cola > Cola > Cola > Cola > Cola
I know this solution is not optimal, because when you are going to drill down the categories in the report visuals, you will keep drilling the cola till the 6th level.

I ended up with this because it's at least readable. because getting the same value is better than getting blanks.

I hope this helps, and I don't know if there is a better solution.

If you found a better solution please let me know, i'm still learning too 😉