Changing the fact table value with the value in the slowly changing dimension table - Urgent

[Anonymous]

Hi @Sean  and others ,

1. I have slowly changing dimension table called SCDOM

      2.  Notice that code DEPT2 is merged with DEPT1 effective 01/02/2017 and inLevel indicates the hierarchy level of organisation unit. SCDOM reflects the changes to the hierarchy levels over time..

3. I have Employee Fact Table which is a snapshot as of period ending.

Level1, Level2, Level3 reflects the hierarchy.

The fact table is not updated for the latest Level information for all levels.

For example the DEPT2 is not changed to DEPT1 as per the SCDOM table at point

User wants to merge the DEPT2 into DEPT1 total based on the SCDOM IsValid for the period.

My Question

1. What I want to do is replace or add a coulmn so that the DEPT2 in the fact table becomes DEPT1 based on the period compared with the SCDOM table. How this can be achieved. ?
2. The same logic I would like to apply for Level1 and Level2 ?
3. Or Is it possible to generate an output like.

What is the best approach for data model or dax expressions or M- query to achieve the above

As I have a very tight time line to deliver a POC by tomorrow, request immediate help.

 Cheers

CheenuSing

[Anonymous]

Hi @Anonymous ,

Any help please.

Cheers

CheenuSing

[Anonymous]

Hi @Eric_Zhang  , @AlbertoFerrari 

Any suggestions please.

Cheers

CheenuSing

[Anonymous]

@Anonymous,

I am not very clear about your logic. Could you please explain that what source tables you use to get the result in the last screenshot?

Regards,
Lydia

[Anonymous]

Hi @Anonymous

The fact table is the Employee Fact Table. at  point number 3.

The slowly changing dimension table at 1 is referenced to find the merging of departments with another department based on a  date.

The L1,L2nd L3 are levels from the EMployeeFact Table. I also have a calendar table which has YearMonth and this is used columns in the matrix table.

Cheers

CheenuSing