Forum Discussion
Building correct relationships between tables
Hello Anonymous , your model and data is completely wrong. You need to do many changes in the tables and the model.
1. Calendar dimension table should not contain duplicates in dates. I can see there are duplicates as it is clearly visibile as many to many relationship. So remove duplicates in that.
2. From Calendar table to Data Table1 create relationship on dates and then from Data Table1 to Data Table2 create relationship using other primary and foreign keys instead of dates. There should be one to many relationship only and keep the cross filtering as both direction always.
3. Dont create any relationship between Data Table 2 and Calendar Table.
If this post helps, then please consider accepting it as the solution to help other members find it more quickly. Thank You!!
Thanks for the reply.
I have done the following:
1. Date in Calendar contains no duplicates
2. Calendar --> Table 2 now linked with Date fields one-to-many relationship
3. Table 1 --> Table 2 now linked via Acccount ID and Medicare ID as key. Many-to-many relationship. Could not make it one-to-many relationship unfortunately.
Unfortunately resulting table is still the same as the original post.
I feel the issue is the relationship between Table 1 and Table 2. I don't think many-to-many is correct, but PowerBI won't allow me to select one-to-many...