Help with connecting relationships between multiple tables

[Anonymous]

Visual of my tables: https://imgur.com/a/gknO4

Quick explanation: There are two companies, Metro and Miner, they handle similar types of trades, but they don't 100% match. I need to be able to tell how many services that Metro completed, could have been handled by Miner. So I'm matching zip codes and trades.

I connected my tables, but when I test my solution by filtering to check trades by Miner location, my filter is not working correctly. For instance there's only 7 trades for Craft, but my filter is showing drastically more. So my relationships aren't working correctly. Any thoughts on how I can fix this?

There are many-to-many relationships, so I had to create lookup tables with unique Zip, Miner Locations, Metro Trades, Miner Trade Types.

Descriptions of each table:

• Metro Trades: a list of Metro trades, list of unique entries.
• Miner Trade Type: a list of Miner trades, list of unique entries.
• MinerMetroTradeMap_Table: matches trades between Miner and Metro.
• Zip: list of unique zip codes
• SPZipCoverage_Table: zip codes that can be completed by Miner
• Miner Locations: list of Miner locations, unique entries
• Trades by Miner Location: a list of trades that each Miner location is able to perform.
• MetroSalesData_Table: this is the base data that I'm testing; in essense, can these jobs be done by Miner?

Side notes:

One Miner location can handle multiple trades.

One Miner location can handle multiple zip codes.

[v-ljerr-msft]

Hi @Anonymous,

Could you share a dummy pbix file and post the expected result against the shared data? So that we can better assist on this issue. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading. 

Regards

[Anonymous]

Here are the links to data and pbix:

data https://1drv.ms/x/s!AkBC9S_qYIPlgfYU2tGiVEoEVet1cg

pbix https://1drv.ms/u/s!AkBC9S_qYIPlgfYTGACuRw6GABOLBg

On the first tab "Yes/No" if you focus on Craft, it currently shows $87 for each trade; this isn't accurate. Correcting this graph will signal a solution.

I've played around with relationships a bit more, so they are slightly different now, but unfortunately it still produces the same result.

redefined relationships: https://imgur.com/QXGHbi8

Yes/No dash: https://imgur.com/b4V5dGq