How to handle slowly changing dimension

[Abhi_PBI]

Hi All,

Hope you are doing well.

I have a report which is dependent on HR data. on my report i have Emp ID (not unique), Emp Grade, Application ID (unique) application date, Status and so on.. The "Grade" field comes from HR data which is a system extract contains employee details including employee ID and Grade. This problem is the employee grade changes over time and the HR data gets updated with new grade replacing old value. But I don't want the emp grade changed for the old records. However if the employee has applied again there will be a new entry on the table with new application ID and i want the new grade to be reflected. While creating the application data base I've created a fixed emp grade table to populate the grade for all existing record. now the idea is if a new record is added the fixed grade Col. will be blank and the New grade col will be filled with updated Grade for all employees. there is one more Col as effective grade which is nothing but  ...

Effective Grade= If(NOT(ISBLANK('Application table'[Fixed grade])),'Application table'[Fixed grade],'Application table'[New Grade])

This is working at the moment. But surely not a correct model. Because if after a year or so the same employee applies again and his grade gets changed then his previous record will be changed because fixed grade col is blank. For example Lets say EMP6 applies again in Oct 2020 and become VP by then. So, along with the new entry the grade against APP011 will also be changed to VP from AVP. This is what i'm trying to avoid. So i need a better and sustainable model. Could anyone please help me with some idea?

 ApplicationTable

Application_ID	EMP_ID	Application Date	Status	Fixed Grade (old record till Aug 2019)	New Grade	Effective Grade
APP001	EMP1	1-Jan-18	Rejected	AM	M	AM
APP002	EMP2	5-Apr-18	Approved	M	SM	M
APP003	EMP3	10-Apr-18	Approved	M	SM	M
APP004	EMP4	11-Nov-18	Approved	AM	M	AM
APP005	EMP3	4-Mar-19	Approved	M	SM	M
APP006	EMP4	6-Jun-19	Approved	M	M	M
APP007	EMP5	29-Jun-19	Approved	AVP	VP	AVP
APP008	EMP7	7-Jul-19	Rejected	AM	AM	AM
APP009	EMP5	8-Aug-19	Approved	VP	VP	VP
APP010	EMP8	22-Aug-19	Approved	AVP	VP	AVP
APP011	EMP6	1-Oct-19	Approved		AVP	AVP
APP012	EMP1	5-Oct-19	Rejected		M	M
APP013	EMP9	3-Nov-19	Pending		SVP	SVP
APP014	EMP2	15-Nov-19	Pending		SM	SM
APP015	EMP10	1-Dec-19	Pending		EVP	EVP

EMP ID	Grade
EMP1	M
EMP2	SM
EMP3	SM
EMP4	M
EMP5	VP
EMP6	AVP
EMP7	AM
EMP8	VP
EMP9	SVP
EMP10	EVP

[Abhi_PBI]

Thanks Maggie.

I'll try implementing this in my actual data and see the result. Thanks for your valuable time.

Regards,

Abhi

[v-juanli-msft · Accepted solution]

Hi @Abhi_PBI 

Create a table

Table 2 = VALUES('Table'[EMP_ID])

Then create measures

Measure =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Application_ID] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[EMP_ID] = MAX ( 'Table'[EMP_ID] )
            && 'Table'[Application Date] <= MAX ( 'Table'[Application Date] )
    )
)

Measure 2 =
CALCULATE (
    MAX ( 'Table'[Application Date] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[EMP_ID] = MAX ( 'Table'[EMP_ID] )
            && [Measure] > 1
    )
)


Measure 3 =
CALCULATE (
    MAX ( 'Table'[New Grade] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[EMP_ID] = MAX ( 'Table'[EMP_ID] )
            && [Application Date] = [Measure 2]
    )
)


Measure 4 = IF([Measure 3]=BLANK(),MAX('Table'[New Grade]),[Measure 3])

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-juanli-msft]

Hi @Abhi_PBI 

Based on my understanding, the system changes employee's grade over time,

but you don't want the new grade to repalce the old one, except when the employee has a new application id.

Right?

However, i have a doubt that since the system changes employee's grade over time, new grade will replace old one,

old one would not remain in system, how can we keep the old one in Power BI?

"the employee grade changes over time and the HR data gets updated with new grade replacing old value"

For the question above, i think we can make a copy of the old records and keep it as a local file.

Do you accept my suggestion or do you take other ways?

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.