Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ianmonat
Helper I
Helper I

Help modelling relationships with 3 fact tables, date table and product tables

Hi - I need help relating tables so that 3 facts sit together in one vizualization. I also need to add Fact 1 + Fact 2, and add them to the vizualization as a 4th Fact as well. 

 

In the schema below:

 

Shipments table contains Fact 1

 

Shipment Plan table contains Fact 2

 

OrderDetail contains Fact 3, and is relating to Shipments through an OrderMaster table by SalesOrder field, which is unique in OrderMaster, but not OrderDetail or Shipments

 

Need Fact 1 and Fact 3 to be added to create Fact 4

 

InvMaster+ relates to all Fact tables by StockCode, which unique in InvMaster+, but not the other Inventory table, InvMaster, hich has additional inventory dimensions

 

I can get the vizualization to work using one fact at a time but having 2 is causing errors like repeition and not relating to dates correctly. 

 

Need to get this fixed fast, will gladly pay a consultant for a web meeting to fix on the spot ASAP.

 

Fact 1 and Fact 2 on a vizualization, Fact 2 not relating to branch or datesFact 1 and Fact 2 on a vizualization, Fact 2 not relating to branch or dates

 

 

 

 

current schema, not workingcurrent schema, not working

 

current relationships, not workingcurrent relationships, not working

6 REPLIES 6

Firstly turn off all bidirectional relationships. Then make sure that the rows in your visuals always come from one of your lookup tables, not any of the fact tables. From what I can see it should be fine. If not, please post the measures you have issues with and any errors. 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.


@MattAllington wrote:

Firstly turn off all bidirectional relationships. Then make sure that the rows in your visuals always come from one of your lookup tables, not any of the fact tables. From what I can see it should be fine. If not, please post the measures you have issues with and any errors. 


 

Hey, I bought your DAX book the other month, it's helped me a lot. 

 

Thanks for the tip, I applied it and got closer to my expected outcome, but am not quite there. Here is the latest schema:

 

revised schema, single direction relationshipsrevised schema, single direction relationships

However when I try to interact facts between tables I get an error like this:

Capture4.JPG 

Trying to add Facts from Shipment and OrderDetail tables that are joined through OrderMaster on SalesOrder

 

and this error:

Capture5.JPG

Trying to get the variance from shipments to the shipment plan, each fact is in different tables.

 

As you can see from the schema, Shipment Plan table is not joined directly to Shipments, as there are no SalesOrders in the Plan. Power BI won't join the table on SKU, there's no other unique field, maybe adding a fake SalesOrder?

 

Any additional insight is much appreciated!

Hi @ianmonat

Thanks for detail representation. In your first formula, the "9L Ships + Orders", "9L Shipments CY" and "SUM9L ORDERS" are calculated column or measure? Based on your screenshot, the relationship between Shipments and OrderDetail tables are many to many. For each row in table Shipments, there are mutiple values related. Which value should be returned when you write the RELATED(OrderDetail[SUM9L ORDERS])? And similar problem for the second formula. Could you please share more information?

 

Best Regards,
Angelia

 

 

 

Further to Angelia's comments, there is no "relationship" between Shipments and Order Detail.  The Related function can only follow relationships from the many side to the one side, so this wont work.  Also it is not clear if you are following best practice in naming conventions.  If you are refering to a measure, you should not mention the table name.  If you are refering to a column you should always use the table name.  Can you clarify.

 

The general principle for a many to many relationship is that you need to iterate over a common lookup table, force context transition and then do the calcuation.  If you already have measures then the context transition will be automatically handled for you.  All you need to do is iterate of the correct table.  Here is the general pattern

 

=sumx(CommonLookupTable,[measure from linked data table 1] - [measure from linked data table 2])



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Hi All - I was able to add the shipments and open orders from the Shipments and OrderDetail table respectively using the formula below. The RELATEDTABLE function worked like magic!

 

Thanks so much for the help. I get that the table names could follow a better convention, will use that advice in the future.  

 

Capture10.JPG

Try deleting the relatedtable function.  I don't see how it can be doing anything in this formula.



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.