Forum Discussion

stangellapally's avatar
stangellapally
Frequent Visitor
10 years ago

Unable to create relationships between tables from SQL Server source

 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

 

 

Need Help

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    10 years ago

    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's avatar
      kazlik
      Helper II
      10 years ago

      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's avatar
        pqian
        Microsoft Employee
        10 years ago

        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. 

    • stangellapally's avatar
      stangellapally
      Frequent Visitor
      10 years ago

      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.