Forum Discussion
Auto-detect cardinality one to one relationships from SQL Server DirectQuery tables?
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....
Community ChampionAnonymous
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.
I don't have a dimension table. I only have three tables.
What should the dimension table have for connecting Customers one-to-many with FoodOrders and DrinkOrders?
- PaulDBrown
Anonymous
We may be having a problem with terminology.
A dimension table, or lookup table, is a table containing at least one field with unique values which is linked to fact tables (where the same field has multiple instances of the same values) by means of a one-to-many (or one-to-one) relationship. Sometimes these dimension tables are called "bridge tables" because they are used to create a "bridge" between two tables containing a common field.
These bridge/dimensiotables are then used in slicers, filters, measures etc to enable the filter conext necessary to allow for measures comparing values from different tables (for example sales from one table vs target from another table by using the "bridge table"/dimension table of "customer".The fields from these Bridge/Dimension tables are the "link" between your fact tables. Without them, your visuals will render the error message you are seeing (unless your measures include an expression such as TREATAS in the filter expression, but that should be a plan "b" or used if a relationship cannot be established using a "bridge table" - but ignore this comment in brackets since we are looking for a solution to the model itself)
As I mentioned in my previous post, can you please post an image of your model (relevant tables), and what fields you are using to create your visual?
I only have three tables from the SQL Server database.
- Customers
- FoodOrders
- DrinkOrders
No error when I click fields on second table.
I get an error when I click a field on the third table.
- PaulDBrown
Anonymous
The question is why do you need to add the CustomerID fields from your "Fact tables" (ie FoodOrder and DrinkOrder tables)?
Once you have the Dimension Table (your "Customers" table), you only use the field (Customers[CustomerID]) from this table in your visual (you don't need the customerID fields from other tables).
The Customers[CustomerID] field in the visual will filter the rows in each fact table to return the corresponding values for other columns you include in the visual. No need to add the CustomerID columns from your fact tables)
I get the error when I add any column. It doesn't matter which column I add.
I'm adding CustomerID fields as a test to see if I get the error.
- PaulDBrown
Anonymous
Ok, I see what you are getting at (I've created a sample PBIX). Sorry about the my confusion.
You are getting the error when you add columns from the tables without an aggregation. This is becasue the structure of the data does not establish a relationhip between each column (food and drink) for the fact tables (they are independent occurrences) . Unless you have a field which establishes "which drink was ordered with each food" (an order ID for example) the visual cannot establish the relationship between the two columns, hence you get the error.
1) You can force an "Artificial" Combination (ie. depict the possible combinations of food and drink for each customer) either by merging the tables in Power Query or creating a measure using the CROSSJOIN function (you can apply this measure in the visual' filter pane).
Crossjoin = CALCULATE( DISTINCTCOUNT(Customers[CustomerID]), CROSSJOIN(FoodOrders, DrinkOrders))But as I say, this is simply a depiction of the possible combinations
2) If you want to see the exact list for each customer, create a new dimension table (I've called it 'Type') by joining both food and drink fields:
Type = UNION(VALUES(FoodOrders[Food]), VALUES(DrinkOrders[Drink]))Now create a relationship between this new table and both your fact tables linking Type with the FoodOrder[Food] and DrinkOrder[Drink]
and use the Customers[CustomerID] and Type[Type] fields to build your visuals:
Apologies again for my initial confusion!