Multiple dates to calculate on in fact table, how to handle the relationships?

[Anonymous]

Hello,

Hope you're doing well. I have two dates in my fact_table that I want to be able to run calculations on, a resolved_date and a created_date. Some properties to note are that there are values for every row of created_date but some resolved_date values are null because, well, it the item hasn't been resolved yet. Both are date types.

I have a date_table with every possible date value. There should be a one-to-many relationship from the date_table's date column to the Fact_Table's resolved_date, and another one to the Fact_Table's created_date. When I try to replicate this in PBI, it only lets me keep one active relationship for the tables.

What is the best way to make it so that both relationships are active? I need to perform calculations on both and then eventually present them on the same graph. I am trying to build out a bridging table and really struggling to do so.

[BiNavPete]

Hi @Anonymous 

You don't need a bridging table. Create the relationships from [DateTable]Date

to each of the dates in the fact table. The second creation will show as inactive with a dotted line in Model View.

When you need to use the active relationship DAX formulae work as normal.
When you need to use the inactive relationship include the USERRELATIONSHIP construct within your CALCULATE measure.
For example - CALCULATE([xxxxx],USERRELATIONSHIP(Date_Table[Date],Fact_Table[Resolved])

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Happy to help!!

Pete
Web: https://binavigation.com
Linked In: https://www.linkedin.com/in/pete-smith-955b73181

[johnt75]

You can only have 1 active relationship between 2 tables, but you can make use of the inactive relationship by using the USERELATIONSHIP function. If the active relationship was with Created then you could create a measure like

Num resolved =
CALCULATE (
    COUNTROWS ( 'Fact_Table' ),
    USERELATIONSHIP ( 'Date'[Date], 'Fact_Table'[Resolved] )
)