Unable to create relationships between tables from SQL Server source

[stangellapally]

 Hello,

I have started uisng Power BI Desktop application recently and trying to create some reports using the application. In that process, I am importing some table from 'Get data- SQL Server source'. Once all the table that were imported I tried to create relation ships between the 'Manage Relationsips option' or 'Create Relationships', for some I was sucessfully able to map the tables and for others even their is a common column it doesnt allow me to map. It say ' its missing some connections' when tried thru or ' relationships between these two columns must have unique values'. The columns that I mapped works when used joins in SQL but Power BI is not smar enough to map it so I tried mapping it manually. Not sure if any had a similar issue? If so can you please suggest me any work around?

Thanks

[Greg_Deckler]

You need unique values on one side of the relationship. Often, you create a separate query and table with only distinct values and map (relate) everything to that. Power BI is pretty smart about transitive relationships.

[stangellapally]

I am creating a this report for Users who need whole universe of data and they want to filter out the values themselves. So they asked used to give all the columns requested from multiple tables.

So their is no work around with out us creating a separate query?

---

@smoupre wrote:

You need unique values on one side of the relationship. Often, you create a separate query and table with only distinct values and map (relate) everything to that. Power BI is pretty smart about transitive relationships.

---

[kazlik]

smoupre have you seen any benefit of using one query like he has now and referencing it then using M to get distinct values for this table instead of two queries?

[pqian]

@kazlik From an evaluation POV it doesn't really make a difference. However, if you reference the query instead of duplilcating it, any changes to the source query will affect the dependents. People often use this to build a report against development server then migrate the source query over to production.