Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 ...
Solved! Go to 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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
95 | |
80 | |
67 | |
62 |
User | Count |
---|---|
138 | |
107 | |
104 | |
82 | |
63 |