Hi guys,
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.
Hey,
If possible drop pbix.
Regards,
Pankaj
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)
Bumping this post for more visibility from our experts and users.
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.
Check this PBIX.
test - Copy.pbix (186.1 KB)
Thanks
Ankit J
Hello @Marcin,
Did the responses above help solve your query?
If not, can you let us know where you’re stuck and what additional assistance you need?
If it did, please mark the answer as the SOLUTION by clicking the three dots beside Reply and then tick the check box beside SOLUTION
Thank you
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.
Thanks
Ankit J
Hi @Marcin
We’ve noticed that no response has been received from you since April 14th.
We waited for the masked demo pbix file, aand any other supporting links and details.
Due to inactivity, we’ll be tagging this post as Solved.
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.
Thanks
Ankit J
Hi @Marcin,
Please provide the requested data set in order for our Experts and community members assists you further in your inquiy.
You may refer to these thread to help you masked your PBIX.
Check out this thread on Tools and Techniques for Providing PBIX Files with Your Forum Questions
Please also check the How To Mask Sensitive Data thread for some tips on how to mask your pbix file.
Not completing your data may sometimes cause delay in getting an answer.
In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.
Hello @Marcin
We notice that no response was received from you on the post above.
We are waiting for your masked demo pbix file and other supporting links and details so other users can help you in your query.
Due to your inactivity, we’ll be tagging this post as Solved.
Should you wish to add your masked demo pbix file and other supporting links and details, you can reopen this thread.
Thank you!