Data Modeling - How do I connect two Facts to two dimensions

[Anonymous]

I have these tables -

FactDemand : ProjectID, Date, some measures

DimProject: ProjectID, some details

FactSupply: ProjectID, Date, some measures

DimCalender: Date

I need to have a comparative view of demand vs supply on timeline for different projects (calculated measures at run time comparing respective measures between FactDemand and FactSupply), so I need them to be related through ProjectID and Date.

More clearly, FactDemand and FactSupply with CalenderTable on Date.

Also FactDemand and FactSupply with DimProject on ProjectID.

How do I model this in Power BI?

I cannot seem to achieve it since it says cannot have two active relationships between same tables (i.e. FactDemand and FactSupply)

How do I go forward with it!!

[v-lionel-msft · Accepted solution]

Hi @Anonymous ,

What you want is a model like this?

If you want to build this kind of table model, there must be a 1: * or 1: 1 relationship between the tables.

Why don't you build relationships directly between Tables "FactSupply" and "FactDemand"? I think this can also achieve your visual effects.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-lionel-msft ,

Thanks for sharing the diagram and (1: * or 1: 1 relationship) tip!

It made me think it is possible what I was looking for.

I actually had developed this habit of selecting the Cross Filter Direction to be Both.

Because of this I couldn't make all these 4 relationships active at once. Once I switched it to Single, it started working.