Forum Discussion
Auto-detect cardinality one to one relationships from SQL Server DirectQuery tables?
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.
Community ChampionAnonymous
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!