Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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! 🙂
Hi @I_NeedMorePower,
Please provide some sample data to help clear your table structure.
Regards,
Xiaoxin Sheng
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
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.
Hi @I_NeedMorePower ,
I am struggling with the same situation you've described. I'm creating a hierarchy and want to replace the BLANKs, but the expression IF(ISBLANK( doesn't read blank results of PATHITEM as BLANK..
Have you found a solution or any workaround?
All best,
Lukas
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 😉
Thanks for reply and explanation 🙂
Actually I came up with similiar solution. I have only 4 levels so it's not that complex and I could work around the problem
1) I created a 3 dummy columns /dum2, dum3 and dum4/ with PATHITEM( table[hiercolumn]; 2/3/4)
2) I created another set of 3 target coulmns /lvl2, lvl3 and lvl4/ with
Hello lukaszcracow
I think I did...
Haha it was long time a go, I don't remember.
But I will look for the file and see what I did last time and reply back to you.
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 🙂
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.
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
117 | |
108 | |
107 | |
92 | |
68 |
User | Count |
---|---|
167 | |
130 | |
129 | |
95 | |
91 |