Forum Discussion

abergman's avatar
abergman
Frequent Visitor
6 years ago

Custom Item Hierarchy

All,

 

I am stuck on a project I'm working on. What I am trying to do is create a custom hierarchy for items and am not sure how to model the data. 

 

I am currently setting up my table to look like this:

Branch | Parent # | Part # | Child # | Item Description | Qty on Hand

 

Ultimately I need my matrix visual in Power BI to look like this:

Parent Item # , Item Description, Qty on Hand

  • Part Item #, Item Description, Qty on Hand
    • Child Item #, Item Description, Qty on Hand

 

For some reason, I can't get anything to work. I've tried creating a custom hierarchy and that doesn't work. I've tried just putting the parent #, part #, and child # columns in the 'Rows' field of a matrix, and that doesnt work either. Everything keeps looking like  where the parent # looks correct, but the part # and child # attributes (item description, qty on hand) are just duplicates of the parent # (see example below). Does anyone have any advice on what I can do?

 

Example of what's happening:

12345     long channel     159,900

  • 12345     long channel     159,900

 

It should be showing this:

12345     long channel     159,900

  • 54216     long angle     67,200

 

Much appreciated.

 

 

Need Help
Tutorial Requests

13 Replies

    • abergman's avatar
      abergman
      Frequent Visitor
      6 years ago

      This is a really good article. I read through it, and it seems to be focused on Excel. Does the solution apply to power BI as well? I'm having a difficult time implementing in Power BI. I created the PATH column, but don't know what to do after that. 

      • ChrisMendoza's avatar
        ChrisMendoza
        Resident Rockstar
        6 years ago

        Power Pivot uses DAX. Yes it applies to Power BI.

        Are you reviewing the Complete Pattern? The only other bit that's not in the article, it's in the comments, is :

         

        BrowseDepth:=
ISFILTERED ( Nodes[Level1] )
    + ISFILTERED ( Nodes[Level2] )
    + ISFILTERED ( Nodes[Level3] )
        MaxNodeDepth:=MAX ( Nodes[HierarchyDepth] )

         If I recall correctly.

        Then you'll make your own hierarchy to drag and drop Levels.

  • v-yuta-msft's avatar
    v-yuta-msft
    Community Support
    6 years ago

    abergman ,

     

    This issue should be related to your custom hierarchy. Could you please share some sample data of the custom hierarchy?

     

    Regards,

    Jimmy Tao