Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Auto-detect cardinality one to one relationships from SQL Server DirectQuery tables?

How can we set up tables in a SQL Server database so Power BI auto-detects cardinality one to one relationships with or without cross filter direction both?   We can get one to many or many to one ...
Need Help
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

Anonymous , You can join Customer with food orders and drink order and analyze data together.

 

Also, assume you take a not summarized column food orders and customer Id from the customer, You can take any aggregated from drink orders

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    amitchandak, When I have a table with fields from Customers and FoodOrders, Power BI gives me this error if I add fields from DrinkOrders.

     

    Can't determine relationships between the fields

    Can't display the data because Power BI can't determine relationship between two or more fields.

     

    amitchandak wrote:

    Anonymous, You can join Customer with food orders and drink order and analyze data together.

     

    Also, assume you take a not summarized column food orders and customer Id from the customer, You can take any aggregated from drink orders



    • PaulDBrown's avatar
      PaulDBrown
      Icon for Community Champion rankCommunity Champion
      5 years ago

      Anonymous 

      In your visuals, you should be using the fields from the Customer table, and then add whatever fields/measures from the other tables. 
      If you do not use the field from the Customer Table to "bridge" the other 2, you will get the error. 
      If you have any other fields common to both FoodOrders and DrinkOrders (for example a date field, postal code etc... you should create dimension tables for these too, and link them in a one-to-many relationship with both fact tables using the common fields.

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        PaulDBrown

         

        I get the error if Customers are one-to-many with FoodOrders and DrinkOrders.

         

        I do not get the error if Customers are one-to-one with FoodOrders and DrinkOrders.

         

        If I have a 100 Order tables, I do not want to manually switch relationships from one-to-many to one-to-one.

         

        Power BI auto-detects one-to-many, but I cannot get it to auto-detect one-to-one relationships from SQL Server databases.

         

        What are a dimension tables?

         

        PaulDBrown wrote:

        Anonymous 

        In your visuals, you should be using the fields from the Customer table, and then add whatever fields/measures from the other tables. 
        If you do not use the field from the Customer Table to "bridge" the other 2, you will get the error. 
        If you have any other fields common to both FoodOrders and DrinkOrders (for example a date field, postal code etc... you should create dimension tables for these too, and link them in a one-to-many relationship with both fact tables using the common fields.