Joining two different database systems

[moesteez]

Hi guys,

I have two databases that Ive linked up and reporting on. One is a point of sale system and the other is an accounting system (xero). Both systems have multiple, but identical, stores.

I want to be able to choose one store for a given date range and see both the sales (point of sale database) and the purchases (accounting database). Im able to do this quite easily by linking each of the databases fact tables to a calendar table if both databases are only limitted to one store.

In order to join both databases to have multiple stores, Ive created a spreadsheet with two columns that links both account IDs from the accounting system and the point of sale system. Power BI wont let me create this extra join on top of the calendar table join because it will "create ambiguity between my tables". But if I drop the join between the calendar table and the accounting system and make this new one active, the report works, except my date slicer no longer works.

I hope this makes sense. 

I have all my filter directions set to "both". Does anyone know how I can get around this so that I can both select a store from both databases as well as filter my reports through the calendar table?

Here is a small part of what my tables look like including the joins between the calendar table and my spreadsheet (sheet 1)

[Mariusz · Accepted solution]

Hi @moesteez 

Try to avoid Bidirectional relationships as they create a lot of problems with data, review all your relationships ( switch from Both to Single direction where possible ) this should remove ambiguity.

Create a Store dimension ( if you haven't yet ) and create relationships ( one to many ) with all the fact tables.

Use dimensions ( like calendar and store ) to propagate the filter.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[moesteez]

Thanks @Mariusz 

That was the problem. Works great now!