Can't make a one to one relation

[rex9311]

Hey all,

I have two Excel data sources:

- Buy-in to retailer

- Sell out to customer

The shared ID is the ASIN which is in both sources. However, when I try to make a one-to-one relationship I keep getting the suggestion that only a many to many is possible. What can I do to make the 1:1 relationship?

Source 1:

Source 2:

Message when trying to create 1:1

Thanks a lot. 

[PaulDBrown]

Create a new dimension table using the New Table function under modeling in the ribbon and:

Dimension sku =
DISTINCT (
    UNION ( VALUES ( 'DE_Detail View_Dia..'[ASIN] ), VALUES ( 'Sheet1'[sku] ) )
)

Now create one-to-many relationships between this table and the corresponding fields in the fact tables. Use the field from 'Dimension sku' in visuals, measures, filters, slicers...

[rex9311]

Hey Paul, thanks for your comment. This was the solution! How do I handle new SKU's that will be added to the sales feeds? Do I need to generate a new table? Or will it update automatically.

[rex9311]

Another question, I have succesfully created the relationship, but it displays the same value if I take a measurement from the other table. Is the relationship in the right direction?

[PaulDBrown]

The relationships are correct. However, you need to use the field from the dimension table, Dimension ID_Table[ASIN],  in the visual instead of the sku field

[PaulDBrown]

The dimension table will update automatically.

[Datavizuserpbi · Accepted solution]

Hello, 

if you want a one to one relationship you have to make sure that you don't have any duplicates in your data so you have to delete the duplicates.

!!

[rex9311]

Hey, hmm I understand thanks. Both are big feeds with sales data that contains duplicates. Is there any other way I can connect them? I want to make a table that displays both the product on a line with sales out and buy-in as columns.

[Datavizuserpbi]

Yes ,create a reference table with unique ID and make one to many relation with both and you gonna have the relation between your product and your sales .