Date table relationships between multiple tables

[Laura_May]

I am trying to connect tables that have a structure where the data has a hierarchical structure, with the projects table ID being the primary key and the parent ID in the milestones table being a foreign key. This is the structure:

I want to be able to use visualisations to filter the data; for example, to create a table visualisation that shows the corresponding milestones / tasks when a project is selected. I also want to be able to filter by date across the three tables. However, when I try to create active relationships between the fact tables and the date table, I get a warning that I cannot create this due to an active set of indirect relationships between the tables. I’m not sure how best to set up the relationships?

[Wilson_ · Accepted solution]

Hi Laura,

The reason you cannot create the relationship between date and all three fact tables is because your filters would be ambiguous. For example, there would be more than one to filter down from the date table to Milestones. If the Date to Milestones relationship was active, how would your model know to use Dates to filter Project, which filters Milestones or to filter from Dates to Milestones directly?

There are almost certainly ways to achieve something like what you want; you just have to be very intentional about how exactly you want the user to interact with the report.

Two possible solutions off the top of my head:

• Create a parameter that can be used to dynamically let Power BI know which date you want to filter by
• Create three different date tables, each individually connected to one of the three tables, if you need to filter by multiple dates to the three tables at the same time

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

P.S. Need a more in-depth consultation for your Power BI data modeling or DAX issues? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.