Filtering two separate date columns using only one active relationship to date dimension table

[tomjneal]

I'm trying to show metrics around turnover, and my business partners want to see a detailed look at who was hired or terminated within a given time period. I have been able to make calculated columns and measures with these two sets of dates (hire and terminationed date) by taking advantage of the USERELATIONSHIP() function within DAX. However, when showing a detailed breakdown, slicers don't include the data from my inactive relationship.

So in this case, they want to see the individuals who terminated within a given time period, but the slicer only brings in those employees that were hired, and leaves out all employees that terminated in the same time period. Besides creating two separate date slicers, is there any way I can bring in both of these dates? Should I duplicate the sample data, the date dimension, or both? I appreciate any help you can give. 

[tomjneal]

Any help on this would be greatly appreciated. Thank you!!

[v-yulgu-msft]

Hi @tomjneal,

You may need to duplicate the sample data table. And establish a relationship between duplicated table and the date dimension table based on terminationed date.

Regards,

Yuliana Gu

[tomjneal]

Yuliana,

Thank you for your help! That worked for the date filter! I'm now able to filter both hire dates and termination dates. However, because the data is a copy, the "Department" and "Job Title" filters no longer function for my table containing terminated employees. When I try and create a relationship between those two data sets, I get an ambiguity error. I'd like to be able to put all of this information on the same page. Is that possible? Thank you so much for all of your assistance!

Very respectfully,

Tom Neal