How to create correct relation ship between tables?

[Anonymous]

Hello!

I created a relationship between tables, but something is wrong.

So I have following tables:

1. "Perspective product" with column "Product" - has 50 unique products.

2. "Correlation" with columns "Product", "Close product", "Correlation coeff-s" - "Product" and "Close products" columns have same values, while "Correlation coeff-s" has coefficients between products from two columns. this table used to see "Close products" and their correlation coefficients when product is selected from the 1st table "Perspective product".

3. "SITC" table with columns "Product code", "Product Name" - contains product codes and their names. Here are approximately 1000 products (unique). "Perspective product" contains only 50 of these products, so they are the same products. I have connected the Product code with  "Perspective product"[Product] to obtain product names and their export values. SITC["Product code'] is connected with a table that has Export values for products.

My question is how can I get product name and export values for the Correlation[Close product] column? My idea is to see Close products with coefficients and their export values for each Product from the Perspective products table. 

So far I tried:

1. I tried to create an inactive relation between Correlation[Close product] and SITC[Product code] and USERELATIONSHIP, but it gives me the wrong values.

2. I duplicated the "SITC" table and connected it separately to the Correlation[Close product] and to the Export values and as result it is s showing incorrect data.

[v-robertq-msft · Accepted solution]

Hi, @Anonymous 

If you want to get the value in a table from a table that is not related and it’s impossible to create a relationship between them, you can try to use the NATURALLEFTOUTERJOIN or NATURALINNERJOIN function in DAX to relate the table manually.

Or you can go to the Power query to use the Merge query to merge the two tables to get the ideal columns:

https://blog.enterprisedna.co/how-to-merge-queries-in-power-bi/

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.