Forum Discussion

rottenheim's avatar
rottenheim
Regular Visitor
8 years ago
Solved

Select correct slowly changing dimension record based on selection

Atm i have 3 tables:   Dim_date   Relevant Fields: Fulldate, Month, Year, LastDateOfMonth   Dim_employee (slowly changing)   Relevant Fields: EmployeeSeqNr, Name, Costcentre, ValidFrom. Valid...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    rottenheim,

    1. Create the following columns in employee table. And change data type of ValidFromInt and ValidToInt to whole number.

    newValidTo = IF(ISBLANK(Dim_employee[ValidTo]),DATE(9999,12,31),Dim_employee[ValidTo])
    ValidFromInt = FORMAT(Dim_employee[ValidFrom],"YYYYMM")
    ValidToInt = FORMAT(Dim_employee[newValidTo],"YYYYMM")

    2. Create YearMonth column and selectedvalue month in DimDate table.And change data type of YearMonth column to whole number.

     

    YearMonth = DimDate[Year]&DimDate[Month]
    selectedvalue = MAX(DimDate[YearMonth])

    3. Create the measure below in employee table.

    Measure = IF(MAX(Dim_employee[ValidFromInt])<=[selectedvalue] && DimDate[selectedvalue]<=MAX(Dim_employee[ValidToInt]),1,0)


    4. Create the following table visual, drag measure to Visual level filters and set its value to 1.


    Regards,
    Lydia

Anonymous's avatar
Anonymous
Not applicable
8 years ago

rottenheim,

1. Create the following columns in employee table. And change data type of ValidFromInt and ValidToInt to whole number.

newValidTo = IF(ISBLANK(Dim_employee[ValidTo]),DATE(9999,12,31),Dim_employee[ValidTo])
ValidFromInt = FORMAT(Dim_employee[ValidFrom],"YYYYMM")
ValidToInt = FORMAT(Dim_employee[newValidTo],"YYYYMM")

2. Create YearMonth column and selectedvalue month in DimDate table.And change data type of YearMonth column to whole number.

 

YearMonth = DimDate[Year]&DimDate[Month]
selectedvalue = MAX(DimDate[YearMonth])

3. Create the measure below in employee table.

Measure = IF(MAX(Dim_employee[ValidFromInt])<=[selectedvalue] && DimDate[selectedvalue]<=MAX(Dim_employee[ValidToInt]),1,0)


4. Create the following table visual, drag measure to Visual level filters and set its value to 1.


Regards,
Lydia

rottenheim's avatar
rottenheim
Regular Visitor
8 years ago
Thank you I am almost there. There is one challenge left When i display insertdate (or enddate) i get to see all records which i want to see. But if i dont visualize those 2 fields some (not all) records disappear from my visual.