Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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.
@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:
However when I try to interact facts between tables I get an error like this:
Trying to add Facts from Shipment and OrderDetail tables that are joined through OrderMaster on SalesOrder
and this error:
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])
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.
Try deleting the relatedtable function. I don't see how it can be doing anything in this formula.
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |