Forum Discussion
Auto-detect cardinality one to one relationships from SQL Server DirectQuery tables?
Community ChampionAnonymous
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.
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.
- PaulDBrown
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....
We want to display fields from the Customers table and multiple Order tables without manually changing relationships to one-to-one.
Power BI only auto-detects one-to-many and one-to-many gives us errors.
Is there a way to auto-detect one-to-one?
Is there a way use one-to-many relationships to display fields from the Customers table and multiple Order tables?
PaulDBrown wrote: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....
- PaulDBrown
Anonymous
Firstly, you should beware of relying on auto-detect table relationships: you might find there are some surprises or relationships created beween fields which aren't "correct" or the desirable relationship.
"Power BI only auto-detects one-to-many and one-to-many gives us errors."
In what sense does a one-to-many relationship give you errors?