Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
EugenioProlog
Helper I
Helper I

How to connect two date columns on a calendar?

I have this scenario where I want to build a panel with two visuals, one displaying the number of contracts started each year, and the number of contracts ended each year.

Below are my tables and relations, where i have a start_date on one table, and an end_date on another table, and I want to connect them to my calendar table. But I can't do that since both tables are connected via the dim table. 

EugenioProlog_0-1752759169355.png

 

Is there a solution for this?

Below is attached my pbix file. 
https://drive.google.com/file/d/1tfDOXMR_0lkoy5AvFq1C7h9LOye_3XXN/view?usp=sharing

 

1 ACCEPTED SOLUTION
anilelmastasi
Super User
Super User

Hello @EugenioProlog ,

 

For this case, there are two options.

 

The first one is

You can create seperate date tables like Contract_End_Calendar and Contract_Start_Calendar. This is the most common and clean solution for multiple date tables.

 

The second one is

You can create one active relationship with fact_contract_start[StartDate]) and one inactive relationship with fact_contract_end[EndDate].  In your DAX measures, activate the second one with USERELATIONSHIP.

Like:

Contracts Ended =
CALCULATE(
    COUNTROWS(fact_contract_end),
    USERELATIONSHIP(fact_contract_end[EndDate], Calendar[Date])
)

 

For reference you can check this:

https://community.fabric.microsoft.com/t5/Desktop/Date-Relationships-Multiple-Tables-Multiple-dates/...

 

If this solved your issue, please mark it as the accepted solution.

View solution in original post

4 REPLIES 4
v-dineshya
Community Support
Community Support

Hi @EugenioProlog ,

Thank you for reaching out to the Microsoft Community Forum.

 

Power BI doesn't allow two active relationships from two different fact tables through a shared dim_contract to a single dim_calendar on two different date fields at the same time.


You have one active relationship per date field at a time, you can create two inactive relationships from dim_calendar[dt_date] to fact_contract_start[start_date] and dim_calendar[dt_date] to fact_contract_end[end_date]. Use USERELATIONSHIP() function in your DAX measures.


Please follow below steps.

 

1. Create two inactive relationships,  Create relationship from dim_calendar[dt_date] --> fact_contract_start[start_date], and set it to inactive. And create relationship from dim_calendar[dt_date] --> fact_contract_end[end_date], and set it to inactive.


Note: You have one active relationship to dim_calendar, so make the both relationships will be inactive, and make activate one in each measure.


2. Create measures using USERELATIONSHIP()

 

Contracts Started =
CALCULATE(
COUNTROWS(fact_contract_start),
USERELATIONSHIP(fact_contract_start[start_date], dim_calendar[dt_date])
)

 

Contracts Ended =
CALCULATE(
COUNTROWS(fact_contract_end),
USERELATIONSHIP(fact_contract_end[end_date], dim_calendar[dt_date])
)

 

I hope this information helps. Please do let us know if you have any further queries.

 

Regards,

Dinesh

DataNinja777
Super User
Super User

Hi @EugenioProlog ,

 

This is a classic challenge when dealing with multiple date fields in Power BI. You are correct that you can't have multiple active relationships from your dim_calendar table to your fact tables, as this creates an ambiguous filtering path. The solution involves using inactive relationships or, for a more robust model, restructuring your data.

A great way to solve this is to keep your current data model but use a combination of one active and one inactive relationship. You would first create an active relationship (a solid line) between dim_calendar[dt_date] and fact_contract_start[start_date]. Then, you would create a second, inactive relationship (a dashed line) between dim_calendar[dt_date] and fact_contract_end[end_date]. Power BI will automatically make the second one inactive. With this setup, you can write DAX measures to count both started and ended contracts.

For contracts started, the measure is simple as it uses the active relationship by default.

Contracts Started = COUNTROWS('fact_contract_start')

For contracts ended, you use the CALCULATE function to change the filter context and USERELATIONSHIP to temporarily activate the inactive relationship for just this calculation.

Contracts Ended = 
CALCULATE(
    COUNTROWS('fact_contract_end'),
    USERELATIONSHIP(dim_calendar[dt_date], fact_contract_end[end_date])
)

A more scalable and recommended best practice is to restructure your data into a single "event" table. In the Power Query Editor, you can transform and append your fact_contract_start and fact_contract_end tables into one new table, let's call it fact_ContractEvents. This table would have a generic Event Date column (containing both start and end dates) and a new Event Type column (with values like "Start" or "End" to identify the nature of each date).

After creating this new table, your data model becomes much simpler. You would create a single, active relationship from dim_calendar[dt_date] to fact_ContractEvents[Event Date]. Your DAX measures then become much more intuitive, as you are just filtering the Event Type column to get your counts.

This measure would count the started contracts from the new combined table.

Contracts Started = 
CALCULATE(
    COUNTROWS('fact_ContractEvents'),
    'fact_ContractEvents'[Event Type] = "Start"
)

And this one would count the ended contracts by filtering for the other event type.

Contracts Ended = 
CALCULATE(
    COUNTROWS('fact_ContractEvents'),
    'fact_ContractEvents'[Event Type] = "End"
)

While the first solution using USERELATIONSHIP is a quick and effective fix, the second solution of restructuring your data is generally considered the best practice. It results in a cleaner, more efficient data model that is easier to manage and extend in the future.

 

Best regards,

anilelmastasi
Super User
Super User

Hello @EugenioProlog ,

 

For this case, there are two options.

 

The first one is

You can create seperate date tables like Contract_End_Calendar and Contract_Start_Calendar. This is the most common and clean solution for multiple date tables.

 

The second one is

You can create one active relationship with fact_contract_start[StartDate]) and one inactive relationship with fact_contract_end[EndDate].  In your DAX measures, activate the second one with USERELATIONSHIP.

Like:

Contracts Ended =
CALCULATE(
    COUNTROWS(fact_contract_end),
    USERELATIONSHIP(fact_contract_end[EndDate], Calendar[Date])
)

 

For reference you can check this:

https://community.fabric.microsoft.com/t5/Desktop/Date-Relationships-Multiple-Tables-Multiple-dates/...

 

If this solved your issue, please mark it as the accepted solution.

AlexTheGreat
Resolver II
Resolver II

Hi,

The first thing you should do is create a combined table with the columns Contract_id, Start_date and End_date. This makes it easier to use in your visuals and also simplifies things if you're going to combine multiple tables later on.

 

Using two dimension tables is not a problem at all (look up "galaxy model"). You might run into issues if you set the relationship direction incorrectly, that can cause problems. But in this case, it shouldn’t be an issue. 

 

Hope this helps! Let me know if you have any further questions.

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors