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 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.

      • lukaszcracow's avatar
        lukaszcracow
        Frequent Visitor
        6 years ago

        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 

    • I_NeedMorePower's avatar
      I_NeedMorePower
      Helper III
      7 years ago

      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 :)