write sql join between two tables from different server in power BI query editor

[jatneerjat]

Hi,

I have two tables which are on different servers and i want to create a relationship between this tables based on a column but there is not any column in either of the table which have unique values so how can i create a relationship or join this table in sql.

Can I create a calculated column based on 2 columns in each table and then create a relationship between this columns?

Thanks,

Neeeraj

[Anonymous]

Hi @jatneerjat

You can't join 2 Many to Many cardinality tables directly. Either you need to create a relationship/ bridge table to cover this OR as you have mentioned, Create a column ( For ex: by concatenating 2 columns) to make th table values as unique .

Thanks
Raj

[jatneerjat]

Hi @Anonymous,

What if i dont have any columns from which i can create a unique column?

Thanks

[Anonymous]

You need to create a bridge/ relationship table.

Thanks

Raj

[jatneerjat]

@Anonymous

but i dont have any columns with unique values in both the tables.

[Anonymous]

So you need to create unique values table, this can be done in 2 ways

1) Write a sql query to get distinct id and then join to the main table 

2)create a dax table  and join the first table , and another join is from Dax table to  second table .

Some thing like below screenshot

For more clarification ,please follow below screen  post :

https://community.powerbi.com/t5/Report-Server/Many-to-Many-Relationship-between-tables-and-show-dat...

[jatneerjat]

@Anonymous,

This is done when we want distinct value for a single column what if i have area and country slicer which are coming from one table and i want to filter another table which have multiple values.I can create a table with distinct values for area amd it can filter both the table but now i also want to filter this two tables and also the area table then how can i do?

Thanks,

Jat

[Anonymous]

You need a single column that has unique value and then join it , it doesnt matter how many columns it has.

If this is not what you are expecting let me know.

[jatneerjat]

@Anonymous,

so now i have 3 tables t1,t2,t3.

t1 has unique [customername] values and other two tables have non-unique [customername] value so i create a relationship between t1 and t2 and t1 and t3 but now i want to filter t3 based on [date] column t2 has.

So is it possible to filter t2 and t3 based on each others column.

Thanks,

Jat

[Anonymous]

My first question is do you a common column to join T2 , t3 and t1, if so keep filters bi-directional , this will filter t3 when t2 is selected.

Thanks,

Aditya 

[jatneerjat]

@Anonymous

So i have created relationship like below,in this i have unique dates in date table and other have duplicate date values:

but i am getting below error while i pull the dates of the columns which have duplicate dates:

how can i create a relationship so all 3 of my dates will be in sync?

Thanks,

Neeraj