Select correct slowly changing dimension record based on selection

[rottenheim]

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 ?

[v-yuezhe-msft · Accepted solution]

@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]

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.

[v-yuezhe-msft]

@rottenheim,

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

Regards,
Lydia

[rottenheim]

Hi,

@v-yuezhe-msft

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

[v-yuezhe-msft]

@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