Custom Item Hierarchy

[abergman]

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.

[v-yuta-msft]

@abergman ,

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

Regards,

Jimmy Tao

[ChrisMendoza]

@abergman -

You should be able to implement similar to https://www.daxpatterns.com/parent-child-hierarchies/

[abergman]

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]

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.

[abergman]

Having trouble with the Node Keys and Parent Keys. My data set is essentially a bill of materials. I have one item that is composed of another item which is composed of another (3 levels total). These are broken down into item numbers.

I created my Path column fine, and my hierarchy depth column worked as well. But when I went to use the code to create my level columns, I used the following code and got an error. Any ideas? Thanks in advance.

[ChrisMendoza]

@abergman -

My guess is you have duplicate values in [SHORT_ITEM_NUMBER]. You should have two tables; one is the structure of the hierarchy and the other is your fact table. Possibly you can share your file?

[abergman]

Currently, I have an ITEM_DIM table, and an ITEM_FACT table. My hierarchy was created within my fact table. Should I have made it in my dimension table? My dimension table just has information like item description, etc.

[ChrisMendoza]

@abergman -

Sound like in your project you'll end up with three tables: [Hierarchy Structure_DIM], [ITEM_DIM], & [ITEM_FACT].

In my implementation, as an example, I have [ORG_STRUCTURE], [EMPLOYEE], & [PAYROLL_FACT_DATA].

[abergman]

Ah, I see. So in my ITEM_STRUCTURE table, I would just have Level 1, Level 2, and Level 3, and the other associated measures and columns used in the article, and that's it? How would I join that hierarchy table to my ITEM_DIM table and my ITEM_FACT table?

[ChrisMendoza]

@abergman -

---

@abergman wrote:

Ah, I see. So in my ITEM_STRUCTURE table, I would just have Level 1, Level 2, and Level 3, and the other associated measures and columns used in the article, and that's it?

---

This is how I implemented in my project. Hard to say if similar would work for you.

---

@abergman wrote:

How would I join that hierarchy table to my ITEM_DIM table and my ITEM_FACT table?

---

Likely, again without knowing your details, ITEM_DIM > ITEM_STRUCTURE > ITEM_FACT.

[abergman]

Yeah that makes sense. To give a few more details on my tables:

ITEM_DIM:

• item #
• item description
• item class
• item subclass
• product group
• product code

ITEM_HIERARCHY:

• item #
• parent item #
• isleaf
• hierarchy path
• hierarchy depth
• level 1
• level 2
• level 3

ITEM_FACT

• qty on hand
• qty on order
• qty on work order
• qty committed
• next month forecast

So I would join my ITEM_DIM table to the HIERARCHY table on the item #. Then, I would join the HIERARCHY table to my FACT table through the same item #. Does that sound correct?

[ChrisMendoza]

@abergman -

Sound about right.

[abergman]

How do I set up the nodes key and parent key for my table?