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

       

       

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        rottenheim,

        Using the sample data in your original post, the measure works as expected even if you exclude ValidFrom and ValidTo fields from the table visual.

        I note that you make changes to the DAX I provided, in my original measure, I compare value of ValidFromInt  with selectedvalue measure, the data type of them are number but not date, please follow the guide in my first reply to create the measure, then check if it works.

        Regards,
        Lydia Zhang