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
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.