Forum Discussion
Connecting 2 tables question
Hi,
i'm hoping this is a straightworward question..
I have data across 2 tables and need to track by products/ ALL suppliers etc. My issue is that i have 2 supplier columns for different parts of each product. see tables example below.
How should I be shaping the data? Do i need to duplcate the first table multiple times or something..etc? Hopefully that's clear..
| Product | Supplier1 | Supplier2 | Date Due |
| Apples | H12 | G23 | 01/01/2022 |
| Oranges | H12 | F34 | 02/01/2022 |
| Pears | K32 | G23 | 03/01/2022 |
| Product | Supplier | Date Delivered |
| Apples | H12 | 02/02/2022 |
| Apples | G23 | 04/01/2022 |
| Oranges | H12 | 03/02/2022 |
| Oranges | F34 | 02/02/2022 |
| Pears | K32 | 02/02/2022 |
| Pears | G23 | 02/02/2022 |
many thanks
J
Hi, Anonymous
You can consider using lookupvalue to create a calculated column in the bill table to integrate the data in the other two tables
Date Due = SWITCH ( 'Bills Table'[Type], "Heavy", LOOKUPVALUE ( 'Table Heavy Products'[Date Due], 'Table Heavy Products'[Product], 'Bills Table'[Product] ), "Light", LOOKUPVALUE ( 'Table Light Products'[Date Due], 'Table Light Products'[Product], 'Bills Table'[Product] ) )Best Regards,
Community Support Team _ Eason
5 Replies
Anonymous , Create common dimension product and supplier and date. join with both tbales
example
Product = distinct(union(distinct(Table1[Product]),distinct(Table2[Product]) ))
refer
Bridge Table: https://www.youtube.com/watch?v=Bkf35Roman8&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=19
for date
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
Thanks @amitchandak
I'll give this a try.
Will the new Product table auto update if new products are added to Table1 after i've made the Product table?
Hi, Anonymous
You can create a Supplier_Product field and create the relationship based on this new field.
But you need to unpivot column"Supplier1" and column “Supplier2” in Table1 first.
Product Date Due Supplier Apples 1/1/2022 H12 Apples 1/1/2022 G23 Oranges 2/1/2022 H12 Oranges 2/1/2022 F34 Pears 3/1/2022 K32 Pears 3/1/2022 G23 Please check my attached file for more details.
Best Regards,
Community Support Team _ Eason
Ok Thanks so much, i think i'm making some progress.
I'm not sure if this is a completely new question or related to the above.
If i have the 3 tables below and i want to track the 4 products to see if they've arrived, or see which suppliers perform well etc, what is the best way to go about this?
Table Heavy Products Product Supplier Type Date Due Apples H12 Heavy 01/02/2021 Oranges G14 Heavy 03/01/2021 Table Light Products Product Supplier Type Date Due Apples H12 Light 01/02/2021 Oranges H12 Light 02/01/2021 Bills Table Product Supplier Type Date Arrived Cost Apples H12 Heavy 01/02/2021 £ 20.00 Apples H12 Light 02/01/2021 £ 30.00 Oranges H12 Heavy 03/01/2021 £ 20.00 Oranges G14 Light Hi, Anonymous
You can consider using lookupvalue to create a calculated column in the bill table to integrate the data in the other two tables
Date Due = SWITCH ( 'Bills Table'[Type], "Heavy", LOOKUPVALUE ( 'Table Heavy Products'[Date Due], 'Table Heavy Products'[Product], 'Bills Table'[Product] ), "Light", LOOKUPVALUE ( 'Table Light Products'[Date Due], 'Table Light Products'[Product], 'Bills Table'[Product] ) )Best Regards,
Community Support Team _ Eason
