Forum Discussion
Cannot create active relationship between date table and multiple dim tables
I am new to data modeling and require help. I have multiple fact tables (Tables - Data_2020, Data_2021, Data_2022, Data_Fifa) year-wise and I want to connect them to one date table, however, the date table establishes an active relationship with only one fact table and an inactive relationship with other fact tables thus giving errors showing the same values across the months. I want to create an active relationship with all the fact tables and am unable to do so.
Thanks.
8 Replies
Can you share a link to a sample PBIX file with sample sources files hosted in a cloud service?
I do not have a Power BI Pro or Premium Per User license. Is it possible to review the file from the below Onedrive link?
Thanks for the PBIX. However, ideally we should be creating the structure in Power Query, and for that I will also need sample files for the different tables (otherwise I cannot work the model in Power Query). Perhaps you can create a zip file with the pbix and the sample files to download?
Partially you re correct to use dimdate. The end goal to use dimntables to avoid many to many relationships. For 90% they are responsible why the relationships are blocked. There should be notification which relationship is blocking you table link, double click on not active ones
What is the column link for relationships between them? Project id or something like that? Try to create new table and use within it dax with distinct union. Connecting them to new dimtable and removing many to many relationships could help.
