Hi, I have three linked tables where I am attempting to create a flow chart linking supplier and retailer locations.
The file I’ve attached here is mock copy from the real file and tables. It appears the linked tables maybe incorrect.
TblAddresss has the SupplierID and their location, this is connected to
TblSupplier with the SupplierID and Supplier Name, this is then connected to
TblShop with SupplierID and Shop Name Test File.pbix (30.1 KB)
But this connection isn’t producing results. Just wondering if I’ve incorrectly linked these tables to display the correct relationships?
Maybe this model is just a representation but I looked at the relationships between the tables and it seems that they have 1-1 bi-directional relationship. If they are indeed 1-1 relationship, I would suggest you bring them all over to a single table as that is the best practice. Also, I notice you have related Supplier column in Supplier Table with Supplier ID in Address table and also with Supplier ID in Shops table. You might want to change the relationship key from Supplier to Supplier ID in Supplier Table. Hope this helps!
Thanks pranamg, those connections were in haste and have reconnected them each between the SupplierID columns.
The original PBI file has all the tables appropriately linked using one to many relationships, but the Address table is linked directly to the Shop Table bypassing the Supplier table per the image below. They also have the Supplier Address location Column copied within the Shop table. I thought might be doubling up information so I decided to connect the Address table to the Supplier table first to improve data efficiency.
However, within their model they have the flow chart working well so will discuss this with them this week to understand their process reasoning.
Thinking how I would apply these connections in Access, I thought the logical thing would be to connect the Address table to the Supplier table, then that to the Shop table. Maybe in PBI this logic doesn’t apply.
In Power BI, the data flows in the direction of the relationship. You can connect Address and Supplier and then connect Supplier to Shops as long as it is 1-M relationship flowing from Address to Supplier to Shops. However, if either of the relationships (Address-Supplier or Supplier-Shops) is 1-1, then just merge the tables so you have just 2 Tables to deal with. Also, I notice you have opted for Bi-Directional relationship which can cause huge issues. I would suggest you change that to a Mono-Directional relationship from 1-M.
Hi @Dplex, did the response provided by @pranamg help in solving your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.
Hi Pranamg, as it turns out after reviewing the actual file, there was inconsistent formatting between two of the linked columns preventing the relationship from displaying values. Once that was sorted, everything worked perfectly. Thanks for your help. It was much appreciated. Cheers.
