It is my first time I need to build a mode with 3 fact tables: sales, shipment and payment. All 3 table have Order IDs, invoices and shipment has their own ID. I spent 2 days trying but there always something wrong with filtering. I know that I need a star schema however I cannot build it in a proper way. I tried many options with some bridge tables etc but there is always something wrong. Can someone guide me, i guess this one of more typical scenarios.
Hello,
Thank You in advance. I made a similar model with some less columns but the general idea is there. It is messed up I guess as i can’t filter matrixes with ID/Invoice number/ Shipment number. Always some relation is missing. I just can’t made a proper bridge tables, I guess. The best would be to have some kind of bridge table which have all these columns, but there are always duplicates in order IDs so it cannot filter in such way. I always get to some blind point. In theory 1 order number can have multiple invoices or shipment numbers. test.pbix (184.7 KB)
Hi @Marcin - What issue are you facing by keeping the Model simple and have one Order ID bridge table connected to others. If there is anything specific not able to achieve then let me know.
Well in this particular data it somehow works but it is not a proper model. The relation between Order ID and Invoices should be 1-Many (not 1 -1) as an order can have multiple invoices. If You set up cardinality like this the whole matrix collapse with error as “there is no connection between tables”
My goal is an opportunity to include columns : order ID, invoice ID and shipment ID in one matrix and to be able to filter all these options but it always end with error.
Hi @Marcin - Can you share sample file with updated data. based on current data, I don’t see an issue. If the relationship is not possible then need to consider creating measure to get the desired result.
The key point was to make a table / matrix and show orders + corresponding invoices and corresponding shipments but it is not possible
There is an information that tables are not connected (invoices and shipment) and there is no connection but still this pretty counter intuitive for me why I can’t just show all info under a order as it filters everything. I am not a newbie but somehow I just don’t know why it happens. It is just a basic thing but maybe I have never use 3 fact tables.
Hi @Marcin - I have tested with your test dataset and it is working and don’t see any reason if you have Order ID in all 3 fact tables and they are not working unless there is some cross filtering happening through other table.
For any further help, please share the actual dataset or atleast a replica of it.