slowly changing dimension and visuals

[gsrk1982]

Dear Experts,

I am working on an HR report and have a type 2/slowly changing table called employee_data_history
(the table has a effectiveFrom and "effectiveto").Data changes per employee infrequently, but when data changes the employee gets a new row where effectivefrom = the date the transaction is entered, and effectiveto = a date in the very distant future.

The employees previous row is updated with the 'effectiveto' being the day prior to the new row.

We have Date dimension in our model I'd like to create a report that shows the count of employees on the selected Date range.

We need to show Active,Leavers etc as measures.In visuals we have to show Age,Gender Mix, Department and Grade count as on selected period.Measure count we can able to filter properly.But if we make visuals its through duplicate.The prior date -1 not filters for all employees.How to filter max(priordate) on selected range using DAX for entire report.

Kindly help me out to overcome this situvation.In T-SQL the following Query working properly as expected how to achive in DAX and powerBI.

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')

Any suggestions Pls..

[v-danhe-msft]

Hi @gsrk1982 ,

Could you please offer a simple sample data to have a test and post your desired result if possible?

And if you could also use the T-SQL statement in Power BI:

Regards,

Daniel He

[gsrk1982]

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.

Emp_ID	Effective_From	Department	Designation	Grade	Join Date	Leaving Date	Leaving Status	Record No	EFFECTIVE_TO	dob
1	01-Jan-2016	Product	System Engineer	100	01-Jan-2016	NULL	0	1	01-Jan-2017	18-Jul-1985
1	02-Jan-2017	Proudct	Sr.System Engineer	110	01-Jan-2016	NULL	0	2	01-Mar-2017	18-Jul-1985
1	02-Mar-2018	R&D	Asst.Manager	210	01-Jan-2016	NULL	0	3	NULL	18-Jul-1985
2	07-Jan-2015	Support	System Engineer	100	07-Jan-2015	NULL	0	1	03-Dec-2015	06-Nov-1978
2	04-Dec-2015	Support	Sr.System Engineer	110	07-Jan-2015	NULL	0	2	09-Oct-2016	06-Nov-1978
2	10-Oct-2016	Implementation	Sr.System Engineer	110	07-Jan-2015	NULL	0	3	24-Mar-2017	06-Nov-1978
2	25-Mar-2017	Implementation	System Analyst	120	07-Jan-2015	NULL	0	4	29-Nov-2017	06-Nov-1978
2	30-Nov-2017	Delivery	System Analyst	120	07-Jan-2015	NULL	0	5	NULL	06-Nov-1978
2	01-Apr-2018	Delivery	Sr.System Analyst	130	07-Jan-2015	NULL	0	6	17-Jul-2018	06-Nov-1978
2	18-Jul-2018	Delivery	Sr.System Analyst	130	07-Jan-2015	NULL	1	7	31-Dec-2013	06-Nov-1978
3	01-Jan-2014	Product	System Engineer	100	01-Jan-2014	NULL	0	1	31-Dec-2014	23-Mar-1981
3	01-Jan-2015	Product	Sr.System Engineer	110	01-Jan-2014	NULL	0	2	30-Nov-2015	23-Mar-1981
3	01-Dec-2015	Product	Sytem Analyst	120	01-Jan-2014	NULL	0	3	17-Aug-2016	23-Mar-1981
3	18-Aug-2016	Product	Sr.Sytem Analyst	130	01-Jan-2014	NULL	0	4	10-Mar-2017	23-Mar-1981
3	11-Mar-2017	Innovation	Sr.Sytem Analyst	130	01-Jan-2014	NULL	0	5	19-Oct-2018	23-Mar-1981
3	20-Oct-2018	R&D	Asst.Manager	210	01-Jan-2014	NULL	0	6	NULL	23-Mar-1981