How to use an inactive relationship to filter dates in a bar chart

[rustyrepublic]

Hi Everyone,

I've been looking for various posts and nothing seems to click regarding how I would solve this issue. I'm really new to DAX and still grappling with the basics

I have a dates table with an active and an inactive relationship to a metrics summary table

The active relationship is between Date & CSA Executed Date 

 

The inactive relationship is between Date & CSA Sent to Site Date 

When I am creating bar charts to report on average time per month to execute CSAs everything is as expected but when I try to create bar charts to report on average time per month to send CSA's to sites I have CSA's that were sent in June showing up in December (as they were executed in December and this is the active relationship between Dates and Site CSA Metrics Summary) 

I figured I could use USERRELATIONSHIP to help me here but I have no idea how to apply this DAX function. Do I create a new measure in the Dates table or in the Site CSA Metrics Summary table, and what am I asking it to calculate?

Any assistance is greatly appreciated 

Thanks

[rustyrepublic]

@amitchandakmany thanks! I had a look at your example and sample file and that was really helpful in figuring it out. 

[amitchandak · Accepted solution]

@rustyrepublic , You need to create a new measure with USERRELATIONSHIP 

refer my blog for example

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

or this

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...