Table Relationship

[Will2023]

Hello, I'm new to PowerBI and have some problem with table relationship. The table relationship concept in PowerBI seems different  with the one in common DBMS.

Suppose I have these simple tables:

Branch

branchid

branchname

Customer

branchid

cutomerid

customername

SalesPerson

branchid

salespersonid

salespersonname

Transaction

salespersonid

customerid

transactionid

transactiondate

qtysold

Branch table has relation with Customer and SalesPerson table on branchid column,.

Customer table has relation with Transaction table on customerid column

SalesPerson table has relation with Transaction table on salespersonid column

I can only make relationship from Transaction table to either Customer or SalesPerson, because ambiguity.

When I filter branch using slicer, only one of Customer or Salesperson got filtered according to which relationship is active.

Is there any workaround to this situation? Thanks.

[DataInsights]

@Will2023,

This is a good example of how the snowflake schema differs from the star schema. In a common DBMS, a snowflake schema is used. But in data analytics, a star schema is a better choice. The approach I would take is to add branchname to tables Customer and SalesPerson (and remove branchid from these two tables). You can then remove the Branch table. The tables Customer and SalesPerson have a 1:* relationship with Transaction, enabling you to filter using any of the fields in Customer and SalesPerson (including branchname).