Auto-detect cardinality one to one relationships from SQL Server DirectQuery tables?

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!