Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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 ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
5 years ago

The suggestions from engineers does not solve the problem.

 

PaulDBrown's avatar
PaulDBrown
Icon for Community Champion rankCommunity Champion
5 years ago

Anonymous 

Ok, fine.

Can you please expand on exactly what you are hoping to achieve (given the structure of your model and data)?

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Take these three tables from SQL Server DirectQuery loaded with relationships detected as one-to-one.

     

    CustomerIdName
    1John
    2Joe
    3Mary

     

     

    CustomerIdFoodOrderIdFood
    111Food11
    222Food22
    333Food33

     

     

    CustomerIdDrinkOrderIdDrink
    144Drink44
    255Drink55
    366Drink66

     

     

    Display this in a report.

     

    CustomerIdNameFoodOrderIdDrinkOrderIdFoodDrink
    1John1144Food11Drink44
    2Joe2255Food22Drink55
    3Mary3366Food33Drink66
    • PaulDBrown's avatar
      PaulDBrown
      Icon for Community Champion rankCommunity Champion
      5 years ago

      Anonymous 

      See if this works: