Forum Discussion

gsrk1982's avatar
gsrk1982
Frequent Visitor
7 years ago

slowly changing dimension and visuals

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..

 

 

Need Help

2 Replies

  • v-danhe-msft's avatar
    v-danhe-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    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's avatar
      gsrk1982
      Frequent Visitor
      7 years ago

      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_IDEffective_FromDepartmentDesignationGradeJoin DateLeaving DateLeaving StatusRecord NoEFFECTIVE_TOdob
      101-Jan-2016ProductSystem Engineer10001-Jan-2016NULL0101-Jan-201718-Jul-1985
      102-Jan-2017ProudctSr.System Engineer11001-Jan-2016NULL0201-Mar-201718-Jul-1985
      102-Mar-2018R&DAsst.Manager21001-Jan-2016NULL03NULL18-Jul-1985
      207-Jan-2015SupportSystem Engineer10007-Jan-2015NULL0103-Dec-201506-Nov-1978
      204-Dec-2015SupportSr.System Engineer11007-Jan-2015NULL0209-Oct-201606-Nov-1978
      210-Oct-2016ImplementationSr.System Engineer11007-Jan-2015NULL0324-Mar-201706-Nov-1978
      225-Mar-2017ImplementationSystem Analyst12007-Jan-2015NULL0429-Nov-201706-Nov-1978
      230-Nov-2017DeliverySystem Analyst12007-Jan-2015NULL05NULL06-Nov-1978
      201-Apr-2018DeliverySr.System Analyst13007-Jan-2015NULL0617-Jul-201806-Nov-1978
      218-Jul-2018DeliverySr.System Analyst13007-Jan-2015NULL1731-Dec-201306-Nov-1978
      301-Jan-2014ProductSystem Engineer10001-Jan-2014NULL0131-Dec-201423-Mar-1981
      301-Jan-2015ProductSr.System Engineer11001-Jan-2014NULL0230-Nov-201523-Mar-1981
      301-Dec-2015ProductSytem Analyst12001-Jan-2014NULL0317-Aug-201623-Mar-1981
      318-Aug-2016ProductSr.Sytem Analyst13001-Jan-2014NULL0410-Mar-201723-Mar-1981
      311-Mar-2017InnovationSr.Sytem Analyst13001-Jan-2014NULL0519-Oct-201823-Mar-1981
      320-Oct-2018R&DAsst.Manager21001-Jan-2014NULL06NULL23-Mar-1981