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.
    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      rottenheim,

      I am not clear about the insertdate or enddate you refer to, could you please post screenshots about your visual?

      Regards,
      Lydia

      • rottenheim's avatar
        rottenheim
        Regular Visitor
        8 years ago

        Hi,

         

        Anonymous

         

        Thanks for your help so far

        I simplified the model a bit.

         

         

        Next i created this measure

         

        ValidTimeSlice = IF(MAX(DIM_EMPLOYEE_HIST[InsertDate]) <= Dim_Date[LastDayOfPeriod] && MAX(DIM_EMPLOYEE_HIST[EndDate]) > Dim_Date[LastDayOfPeriod] ;1;0)

         

        Then i created the visual

         

         

        This works. But i only wanted to show the data. So not the Insertdate and enddate.

         

         

        See what happens to nr 1002 and 1005 ? They are gone.

         

        I think when the timeslice is the last it will show and if it isnt it will only show when insert (or enddate) are being shown.

        Somehow powerbi seems to do a group by without taking insertdate (or enddate) into consideration unless i display them.

        The reason would be in the usage of MAX(DIM_EMPLOYEE_HIST[InsertDate])

         

        It all makes sense but i would like to select the 'valid' record without having to show the insertdate (or enddate)

         

        Regards

         

        Remy