Connecting 2 tables question

[Anonymous]

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

[Anonymous]

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

[v-easonf-msft · Accepted solution]

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

[Anonymous]

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?

[v-easonf-msft]

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

[amitchandak]

@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.