Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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)
Solved! Go to 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.
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |