Forum Discussion
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. ValidTo
SampleData:
1, SomeName, 123, 1-jan-2017, 1-jun-2017
1, SomeName, 456, 1-jun-2017, null
Fact Hours:
SeqNr, Date, EmployeeSeqNr, NbrOfHours
Between Dim_employee and Fact Hours I put in a calculated table containing employee numbers.
What I want:
User selects a Year / Month
Based on the selection I need the hours of the selected month. This parts wordks easy in the model
But I also need to display employee record which is valid on the last day of the selected on month
So if I select april 2017 I want to see costcentre 123
And if I select dec 2017 I want to see costcentre 456
How to achieve this ?
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
5 Replies
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- 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.
rottenheim,
I am not clear about the insertdate or enddate you refer to, could you please post screenshots about your visual?
Regards,
Lydia
