how to relate two databases

[CLCastroEn]

Hello everyone!

I am working with a database that has the following data:

SKU, Store, Week, CLiente, , Category and Family. Where category and family are logically SKU hierarchies. The thing is, I have another database that only brings SKUs, Customer, Week, Sales 2019, and Sales 2020.

When making the relationship in power BI, I make a relationship with the common point (SKU), which is a many-to-many relationship because on both bases there are duplicate skus (a sku can be equal for two or more customers). I made the relationship because I need to know the sales of 2019 and 2020 by SKU (which in a way I think works for me) but when I put a category filter, the tables do not move and there is a constant value that obviously did not work the filter. They can help me with relationship building!

where "Internal Code" in table "sheet1" is the same as "material" in the "base" table, the relationship is many to many.

[amitchandak]

@CLCastroEn , maybe you try a composite key and try to make it one to many

example

key = [SKU] & " " & [Year]

[CLCastroEn]

@amitchandak Hello! but anyway it would be several to several. because on both bases the SKU is repeated for different weeks, different customers.

Maybe if I create a table with the composite key "SKU & Year & Customer" where there are only unique values? Some kind of bridge between the two bases and there if the one-to-many relationship would work?

Best regards