Relationship issues - one table to rule them all.

[Anonymous]

I'm not sure I'm going about this correctly. I have one table I created in Excel and then imported to my PBI report. It takes the location ID data that I recieve in many different reports and relates it to a friendly name. I've connected it to all the other tables that I get data from. As you can see, I've mapped the locations (highlighted in yellow) and currently they are all in bi-directional filtering. I'm probably not doing that right but, I couldn't get one table to share to another without it. But maybe that's the problem. When I grab Location from the location mapping table and then use only the items in one of the other tables, everything matches up exactly as it looks in Excel. However, if I grab location from the Location Mapping, and Purchase Order and Work Order numbers from the PO Data table, it doesn't match correctly to the work order summay in the WO data table. In fact, I've grabbed work order number from both the PO and WO tables and get complete mismatches. I'm also trying to build relationships with vendor name. I have a table that is all the vendor names and their ID numbers. For whatever reason, I can map it as a many to one from WO PO GR to the vendor table (not pictured) but I get a many to many error when I map Vendor Invoices vendor name to Vendor Mapping vendor name. I thought that maybe, at least for the WO issue, I might need to create separate table of WO and PO numbers that would then link back to other reports. So that way I could match the WO in the WO data table to it and then match that back to the PO data table and thus get the correct summary and work type. I'm just really confused. Honestly, doing this all in Excle and then loading it to PBI was easier, but it's overly complicated for someone not familiar with the process. I'm trying make this easier and allow for raw data dumps so anyone can get the data and populate a report. I've not had a day off in 18 months and I'd really like to take one. So, any help or pointing in the right direction would be much appreciated. 

[MFelix · Accepted solution]

Hi @Anonymous ,

Having the bidirectionatily turn on creates a lot of issues since the filtering is done for both sides this creates strange results.

Believe that in this case you need to take out the bidirectionality and add additional relationships between tables that assume that theres is information related or the use of measures to make the calculations.

In the example you gave about the Purchase orders and Work Order and Work order summary, you must create a dimension table that relates this two/three tables besides the location that will allow to filter the information on the next level.

Not sure if what I'm saying after is correct but assuming that you have the Work order ID in both Purchase order and Work order if you relate both of them by a unique identifier with a one to many relationship making the crossing with the location you will get the correct result.

Another option is to use Power Query to transform the data to return the result of the several tables into a single table has you refer you would do in Excel, however instead of using VLOOKUP or SUM, you would use the query editor.

If you can explain a little bit better your model and the way you need to have the information related I can try and guide you.