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 by adding Primary Keys and Foreign Keys, but cannot get one to one relationships. We need one to one relationships so we can display columns from all three of the following tables. - Table Customers has CustomerId as Primary Key- Table FoodOrders has CustomerId as Foreign Key- Table DrinkOrders has CustomerId as Foreign Key We can only display columns from Customers and either FoodOrders or DrinkOrders.

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

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 fieldsCan'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

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.

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.

Anonymous One-to-one is fine: it just means that both tables have unique values. So you can have relationships which are one-to-one or one-to-many. What you need to avoid at all costs are Many-to-Many relationships, since these can cause havoc in calculations. Dimension tables have unique values of a particular field (and can have more than one field) which is then used to create a relationship with other table(s). These dimension tables make the model efficient, and are used for slicers. filters, filter expressions in measures....

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

20 Replies

amitchandak
Super 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
  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
    Community 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.
v-xicai
Community Support
5 years ago
Hi Anonymous ,

Do the suggestions from engineers make sense? If so, kindly mark the proper reply as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

Best regards

Amy
- Anonymous
  Not applicable
  5 years ago
  The suggestions from engineers does not solve the problem.
  - PaulDBrown
    Community Champion
    5 years ago
    Anonymous
    
    Ok, fine.
    
    Can you please expand on exactly what you are hoping to achieve (given the structure of your model and data)?

Forum Discussion

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

20 Replies

Related content

Relationships/Cardinality

Bidirectional relationship cardinality issue

Relationships cardinality

Relationship Cardinality

BUG::Relationship cardinality issue