Linking Parent and Child Items, Data in the same Table

[EUhlich]

Hello, I've got a problem I'm not able to solve...

In my source database, I have one table that contains all item information. It's connected via DirectQuery to PowerBI Desktop.

Generally speaking, the table consists of items with different hierarchies.
Something like this:

Items

ItemID	ItemName	ParentID	Hierarchy
1	Shorts Brown | XL	3	1
2	Shorts Brown | M	3	1
3	Shorts Brown	7	2
4	Shorts White | S	6	1
5	Shorts White | L	6	1
6	Shorts White	-	2
7	Shorts	7	3

I know it's not the best approach to storing the data, but I cannot change this source data.

Then I've got some other tables that contain customer order data. For Example:

Order Lines

OrderID	OrderLineID	ItemID	PriceGross
1000	10000	2	15
1001	10010	1	10
1002	10015	5	20

Now, I would like to be able to analyze the data based on the items I have.

I want to click ItemID 2 to get the price of 15.

I want to click ItemID 3 to get the summarized price of 25 for the "Shorts Brown".

I want to click ItemID 7 to get the summarized price of 45 for all sold "Shorts".

Up until now, I was not able to get this running.

I've tried with "importing" the same Items table multiple times, linking the different tables using the ItemID and ParentID, but I didn't get it to work.

I think this might be the way to go, but I'm not able to set this up correctly.

Any help is much appreciated.

Erik

PS: Sorry for the table formatting ... it looks different in the Edit Mode ...

[EUhlich · Accepted solution]

Hi @lbendlin ,

thank you for your reply as well as the solution which really seems to be working when using the right mode! Unfortunately, I can not use the Storage Mode Import for the tables in question (Items & Order Lines).

But I think I managed to get what I need (although I'm not 100%  satisfied).
I just connected the Items table several times and used DirectQuery for every additional table.

Then I linked the tables with each other:

- OrderLines.ItemID < Items.ItemID

- Items.ItemID <> Items(1).ItemID

- Items(1).ParentID <> Items(2).ItemID

- Items(2).ParentID <> Items(3).ItemID

Maybe this is helpful to others as well who purely use DirectQuery.
I'm not saying that this is the best approach and I'm really sure it isn't. But maybe it's something to work with.

[lbendlin]

Your hierarchy column isn't really needed.  Here is a sample implementation based on PATH().  You may need to check if that will work with your data source (make sure to enable the local data model option). See attached.