cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rottenheim
Regular Visitor

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.

 

 Capture.PNG

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 ?

 

 

1 ACCEPTED SOLUTION
v-yuezhe-msft
Microsoft
Microsoft

@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.
1.JPG2.JPG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
v-yuezhe-msft
Microsoft
Microsoft

@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.
1.JPG2.JPG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi,

 

@v-yuezhe-msft

 

Thanks for your help so far

I simplified the model a bit.

 

model.JPG

 

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

 

WithInsertdate.JPG

 

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

 

WhInsertDate.JPG

 

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

 

 

 

 

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.