Forum Discussion
Auto-detect cardinality one to one relationships from SQL Server DirectQuery tables?
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 ...
PaulDBrown
Community Champion
Community ChampionAnonymous
Ok, fine.
Can you please expand on exactly what you are hoping to achieve (given the structure of your model and data)?
Take these three tables from SQL Server DirectQuery loaded with relationships detected as one-to-one.
| CustomerId | Name |
| 1 | John |
| 2 | Joe |
| 3 | Mary |
| CustomerId | FoodOrderId | Food |
| 1 | 11 | Food11 |
| 2 | 22 | Food22 |
| 3 | 33 | Food33 |
| CustomerId | DrinkOrderId | Drink |
| 1 | 44 | Drink44 |
| 2 | 55 | Drink55 |
| 3 | 66 | Drink66 |
Display this in a report.
| CustomerId | Name | FoodOrderId | DrinkOrderId | Food | Drink |
| 1 | John | 11 | 44 | Food11 | Drink44 |
| 2 | Joe | 22 | 55 | Food22 | Drink55 |
| 3 | Mary | 33 | 66 | Food33 | Drink66 |
- PaulDBrown
Anonymous
See if this works: