Forum Discussion
Auto-detect cardinality one to one relationships from SQL Server DirectQuery tables?
Community ChampionAnonymous
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?
- Customers is one-to-many to FoodOrders
- Customers is one-to-many to DrinkOrders
- Report Visual Table contains fields from Customers and FoodOrders
Given above, Power BI gives below error when I add fields from DrinkOrders to the Report Visual Table.
Can't determine relationships between the fieldsCan't display the data because Power BI can't determine relationship between two or more fields.
- PaulDBrown
Anonymous
Can you please show the model view for the affected tables + an example of the visual and specify which fields you are using and from which tables?
If the model is set up correctly you will not get this message. It depends on the bridge tables (dimension tables) you have to relate the fields,
If you try to create a visual which does not include fields from bridge/dimension tables, you get this error.