slowly changing dimension and visuals

Dear Community,

Find the following Data Set Employee History Table and I have Date table.

Here employee attributes will change  according to effective from.I have between slicer in report.

I want to build something that I can use a date and time slicer to see if this record was "active" within this range and need to show visuals of department,Designation,Age in visuals.The challenge is in scorecard we can able to get desired distinctcount but in visuals we found duplicate. I want to Filtering entire report based on a in between time from DataSet.We are using SQL Server

In T-SQL we can able to filter our desired recordset

select * From history h1 where effective_from=

(select max(Effective_From) from history h2 where h1.Emp_ID=h2.Emp_ID and h2.Effective_From<'01-apr-2018')

But how to do the same and achive the same in report.

DirectQuery method.