Forum Discussion

I_NeedMorePower's avatar
I_NeedMorePower
Helper III
7 years ago

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 "RetailProductHierarch...
Need Help
Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi I_NeedMorePower,

 

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

 

Regards,
Xiaoxin Sheng

  • I_NeedMorePower's avatar
    I_NeedMorePower
    Helper III
    7 years ago

    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_NeedMorePower's avatar
    I_NeedMorePower
    Helper III
    7 years ago

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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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_NeedMorePower's avatar
        I_NeedMorePower
        Helper III
        7 years ago

        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.