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.
Hi
I've been trying to solve this for the past coulple of days, but unsuccessfully. I've got the following 3 tables:
- table "Items Dimensions" with Items and their dimentions
- table "Supplier ABC" for services 1 and 2 for the years 2020 and 2021 where the rates are based on the product of the dimentions (L x W x H) from the 1st table
- table "Supplier XYZ" for service 3 for the years 2020 and 2021 where the rates are based on the item length (from 1st table), as well as origin and destination
What I'm trying to achieve is when I select Item ID from the 1st table, as well as the Origin and Destination from the 3rd table - to get the corresponding rates for services 1, 2 and 3 in 2020 and 2021 and to be able to build a stacked column chart that will show each cost in the two years.
ITEM DIMENSIONS TABLE
Item ID | Length | Width | Height | L x W x H |
A | 100 | 10 | 5 | 5,000 |
B | 150 | 15 | 10 | 22,500 |
C | 200 | 20 | 20 | 80,000 |
D | 200 | 20 | 20 | 80,000 |
SUPPLIER ABC
Year | Service | L x W x H From | L x W x H To | Rate |
2020 | Service 1 | 0 | 50,000 | $ 1,000 |
2020 | Service 1 | 50,001 | 100,000 | $ 2,000 |
2020 | Service 2 | 0 | 50,000 | $ 3,000 |
2020 | Service 2 | 50,001 | 100,000 | $ 3,500 |
2021 | Service 1 | 0 | 50,000 | $ 1,700 |
2021 | Service 1 | 50,001 | 100,000 | $ 2,900 |
2021 | Service 2 | 0 | 50,000 | $ 3,400 |
2021 | Service 2 | 50,001 | 100,000 | $ 5,000 |
SUPPLIER XYZ
Year | Service | Length From | Length To | Origin | Destination | Rate |
2020 | Service 3 | 0 | 100 | Location 1 | Location 2 | $ 100 |
2020 | Service 3 | 101 | 200 | Location 1 | Location 3 | $ 400 |
2020 | Service 3 | 201 | 400 | Location 1 | Location 4 | $ 600 |
2020 | Service 3 | 0 | 100 | Location 2 | Location 1 | $ 100 |
2020 | Service 3 | 101 | 200 | Location 2 | Location 3 | $ 300 |
2020 | Service 3 | 201 | 400 | Location 2 | Location 4 | $ 700 |
2021 | Service 3 | 0 | 100 | Location 1 | Location 2 | $ 220 |
2021 | Service 3 | 101 | 200 | Location 1 | Location 3 | $ 540 |
2021 | Service 3 | 201 | 400 | Location 1 | Location 4 | $ 760 |
2021 | Service 3 | 0 | 100 | Location 2 | Location 1 | $ 210 |
2021 | Service 3 | 101 | 200 | Location 2 | Location 3 | $ 350 |
2021 | Service 3 | 201 | 400 | Location 2 | Location 4 | $ 910 |
Can anyone, please, help with this, as I can't wrap my head around how to solve it...
Many thanks!
Hi @BlueGorilla ,
As @lbendlin said, your description does not correlate well with the three tables provided to know the exact inter-table relationships.
How to Get Your Question Answered Quickly - Microsoft Power BI Community
Best Regards,
Henry
There is no obvious way to control the SUPPLIER ABC fact table from the SUPPLIER XYZ fact table. So your statement: " when I select Item ID from the 1st table, as well as the Origin and Destination from the 3rd table" is not something that is supported by your data model. Please reconsider your requirement or provide more details.
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 |
---|---|
114 | |
97 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |