Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am looking for some general advice. I have a moderate sized model (about 20 tables). Most tables link into the order table in a normal star schema but the order table then links down furth into the order line table. So the order line table is my basic fact table. Both the order table and the order line table hold the transaction date. Sometimes I am reporting at the order level (with no reference to the detail in the order lines) but sometimes I am reporting at the order line level.
Where would you link the calendar date - into the order table or into the order line table? Or does it make no difference to efficiency which one I use?
Hi @ruthhacche ,
Please kindly refer to:
https://exceleratorbi.com.au/multiple-relationships-between-tables-in-dax/
It teaches you about Date Table Relationship to Multiple Fact Tables.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Does one run more efficently that the other - eg when manipulating pivots in an excel spreadsheet derived from the dataset?
Thank you for responding but this does not answer my question. I want to understand whether the calendar should link into the order table OR into the order line table for most efficient handling. I totally understand the calendar cannot link actively into two tables at once.
@ruthhacche , depends. What Date is most needed? I would have preferred the order header as most of the filter will coming on the order date. For any line-specific filter, I can use the line date itself.
@ruthhacche , Ideally in star schema both order header and lines should independent tables. You can merge columns in power query(what ever you need from another table), but on the model view, they do connect with each other.
As for the date table, you can not have two paths with order lines. one direct and one via order header. So one of the joins will become inactive or not work
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |