Filter two fact tables via the same date table

Hi PaisleyPrince,

This is a classic data modeling scenario in Power BI, and the solution depends on how you want the Date slicer to behave.

You essentially have two different date meanings:

Project Start Date (FactProjects)

Invoice Posting Date (FactInvoices)

Because these represent different business concepts, the recommended approach is to model them explicitly, not try to overload a single relationship.

✅ Best practice solution (Recommended)
1⃣ Use a single Date table with two relationships

Create two relationships from the same Date table:

Date[Date] → FactProjects[ProjectStartDate] (Active)

Date[Date] → FactInvoices[InvoiceDate] (Inactive)

Power BI allows only one active relationship, but this is expected and correct.

2⃣ Control which date is used via measures

For measures that need to respect Invoice Date, activate the inactive relationship using USERELATIONSHIP.

Example:

Total Invoiced Fees :=
CALCULATE (
SUM ( FactInvoices[InvoiceAmount] ),
USERELATIONSHIP ( Date[Date], FactInvoices[InvoiceDate] )
)

For measures based on Project Start Date, use the active relationship normally:

Total Project Fees :=
SUM ( FactProjects[TotalFees] )

This gives you full control over which date logic applies per measure.

3⃣ One Date slicer, multiple meanings

With this approach:

The same Date slicer filters:

Project-based measures by Project Start Date

Invoice-based measures by Invoice Date

Each measure behaves correctly and independently

🟡 Alternative (when users must choose the date context)

If users explicitly need to switch between “Project Date” and “Invoice Date”, then use:

A disconnected slicer (Date Type)

A SWITCH() inside measures to decide which relationship to activate

But this adds complexity and is only needed if the business requirement demands it.

🚫 What NOT to do

❌ Do not duplicate Date tables unless absolutely required

❌ Do not relate both fact tables using the same date column semantics

❌ Do not try to filter invoice data by project start date implicitly

These patterns cause ambiguity and incorrect totals.

✅ Summary

One Date table ✔

Two relationships ✔ (one active, one inactive)

USERELATIONSHIP for invoice-based measures ✔

Clean star schema preserved ✔

If this answers your question, please give Kudos 👍 and mark this reply as the Accepted Solution ✔ so it can help others facing the same modeling challenge.