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.

To add what Kejmil said, I think you would use the fee date to join to the date column on the date table.  the date table will still be able to be related to the project start date no problem. Make sure the date table filters the fact tables.  

Hi,

Share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

Since those are two separate fact tables, can't you just create relationship between the date and those tables? If you need to break the values down by project id, you can just create another dim table for that.

PaisleyPrince  Hey,

• Keep relationships from the single Date table to each fact inactive.
• Add a selector table: DateType = { "Project Start", "Invoice Date" } and a slicer on it.
• Wrap measures with USERELATIONSHIP to activate the chosen date context:

• Example:

- Relationships (inactive):
- Date[Date] — Projects[StartDate] (inactive)
- Date[Date] — Invoices[InvoiceDate] (inactive)
- Selector:
- DateType = DATATABLE("Type", STRING, {{"Project Start"}, {"Invoice Date"}})
- Measures:

Fees by Selected Date =
VAR sel = SELECTEDVALUE(DateType[Type], "Project Start")
RETURN
SWITCH(
sel,
"Invoice Date", CALCULATE([Total Fees Invoices], USERELATIONSHIP(Date[Date], Invoices[InvoiceDate])),
CALCULATE([Total Fees Projects], USERELATIONSHIP(Date[Date], Projects[StartDate]))
)​

Thanks

Haish K 

If I resolve your issue. Kindly give kudos to this post and accept it as a solution so other can refer this.

PaisleyPrince  Hey,

• Keep relationships from the single Date table to each fact inactive.
• Add a selector table: DateType = { "Project Start", "Invoice Date" } and a slicer on it.
• Wrap measures with USERELATIONSHIP to activate the chosen date context:

• Example:

- Relationships (inactive):
- Date[Date] — Projects[StartDate] (inactive)
- Date[Date] — Invoices[InvoiceDate] (inactive)
- Selector:
- DateType = DATATABLE("Type", STRING, {{"Project Start"}, {"Invoice Date"}})
- Measures:

Fees by Selected Date =
VAR sel = SELECTEDVALUE(DateType[Type], "Project Start")
RETURN
SWITCH(
sel,
"Invoice Date", CALCULATE([Total Fees Invoices], USERELATIONSHIP(Date[Date], Invoices[InvoiceDate])),
CALCULATE([Total Fees Projects], USERELATIONSHIP(Date[Date], Projects[StartDate]))
)​

Thanks

Haish K 

If I resolve your issue. Kindly give kudos to this post and accept it as a solution so other can refer this.