powerbi - How to model a M:M using bridge table into a star schema when there is a need for bidirectional relationships? - Stack

I have FactSales and FactCharges. Granularity of FactSales is OrderId+OrderLineId. Granularity of FactCharges is OrderId+ChargeDesc.

There is a M:M relation between them on OrderID.

I have a table visual that shows the OrderId, OrdNum, sum(Sales amt) [from FactSales] and ChargesDesc, Sum(ChargeAmt) [from FactCharges]. On the visual, charges do repeat for each order line within the Order, but the totals are correct as Power BI handles M:M correctly, this is as expected.

To improve the model, I have removed this M:M relationship, and created a BridgeTable containing the OrderId. Thus we have a 1:M relation from the BridgeTable to Fact tables.

Next, I have hidden this bridge table, and also hidden the OrderId in FactCharges table. In my visuals I use the OrderId from FactSales table. Now so that Sales can filter Charges, I have enabled the bidirectional relationship between FactSales and BridgeTable.

So that selecting a Charge desc shows all corresponding sales, I have also enabled the bidirectional relationship between BridgeTable and FactCharges.

Is this a star schema and if not then how to make it a star schema?

I did think of unhiding the BridgeTable, and using the OrderId from BridgeTable into my visuals, which means Power BI will auto filter both Fact tables, however this won't help me achieve the goal of Charge desc filtering sales rows.

I have FactSales and FactCharges. Granularity of FactSales is OrderId+OrderLineId. Granularity of FactCharges is OrderId+ChargeDesc.

There is a M:M relation between them on OrderID.

I have a table visual that shows the OrderId, OrdNum, sum(Sales amt) [from FactSales] and ChargesDesc, Sum(ChargeAmt) [from FactCharges]. On the visual, charges do repeat for each order line within the Order, but the totals are correct as Power BI handles M:M correctly, this is as expected.

To improve the model, I have removed this M:M relationship, and created a BridgeTable containing the OrderId. Thus we have a 1:M relation from the BridgeTable to Fact tables.

Next, I have hidden this bridge table, and also hidden the OrderId in FactCharges table. In my visuals I use the OrderId from FactSales table. Now so that Sales can filter Charges, I have enabled the bidirectional relationship between FactSales and BridgeTable.

So that selecting a Charge desc shows all corresponding sales, I have also enabled the bidirectional relationship between BridgeTable and FactCharges.

Is this a star schema and if not then how to make it a star schema?

I did think of unhiding the BridgeTable, and using the OrderId from BridgeTable into my visuals, which means Power BI will auto filter both Fact tables, however this won't help me achieve the goal of Charge desc filtering sales rows.

• powerbi
• powerbi-desktop
• data-modeling
• microsoft-fabric

Share Improve this question Follow edited Feb 2 at 14:10 variable asked Jan 30 at 2:33 variablevariable 9,7762626 gold badges143143 silver badges315315 bronze badges 4

• These are contradictory statements: "Granularity of FactCharges is OrderId." "There is a M:M relation between them on OrderID." If the grain of Charges is OrderID, then the relationship would be 1 charge to many order line items. – David Browne - Microsoft Commented Feb 1 at 21:24
• Think of it this way. Granularity of FactSales table of OrderLineId. Granularity of FactCharges table is OrderId. The charges are at the OrderId level and need to be applied to each OrderLineId from FactSales. But the sum of charges should not increase. Basically this is a classic M:M between FactSales and FactCharges on OrderId. – variable Commented Feb 2 at 6:13
• If “the granularity of FactCharges is OrderId” then there is only one row in FactCharges for each OrderId. So the relationship is 1:M, not M:M. – David Browne - Microsoft Commented Feb 2 at 13:26
• Ah sorry what I meant is that table has charges at OrderId level, so technically the granularity of FactCharges is OrderId and ChargeDesc. Sorry about this I'll update the question then. – variable Commented Feb 2 at 14:09

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

Cannonically you would have a fact table at the OrderId grain and a fact table at the (OrderId,OrderLineId) and a fact table at the (OrderId,ChargeDesc) grain. At the OrderId grain you typically have some order-level metrics, and dimensional attributes like OrderDate that don't vary among the line items.

FactSalesOrder(OrderId)
FactSalesLineItem(OrderId,LineItemId)
FactSalesCharges(OrderId,ChargeDesc)

"the goal of Charge desc filtering sales rows" can be accomplished by making the 1:M relationship between FactSalesCharges and FactSalesOrder bi-directional.

FactSalesOrder --> FactSalesLineItem
FactSalesOrder <--> FactSalesCharges