Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
BIHall
Regular Visitor

Human Resources Sample File - Employee Table

Hi Everyone

 

I've been looking at the Human Resources Sample File today in PowerBi Desktop and I think I understand how eveything works and how I could build something similar using my own organsiations data.

 

One thing I'm struggling why we need the date field as the fist field in the employee table, if we didnt have that there and it was just the employee ID what would happen?

 

Capture.PNG

 

 

3 ACCEPTED SOLUTIONS
v-chuncz-msft
Community Support
Community Support

@BIHall,

 

You may just disable the relationship to see the difference.

Community Support Team _ Sam Zha
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

 

 

Now Understood: The Date column in the Employee Table is used as follows:

 

In this dataset, once a month, an extract from the source system is produced. For each month that an employee is active they have a line of data on the first of the month as an employee “snapshot” fact table.

 

I read the "Data Warehouse Toolkit by Ralph Kimball and Margy Ross which has a small but useful section on thinking about models for HR and how to strucutre them.

 

powerbi hr file.PNG

View solution in original post

Anonymous
Not applicable

Hi @Anonymous 

 

I cant find the original post I personally found a soultion for, but this is what you can do, create these 4 messurements,

 

New Hires = COUNTA ("employment hire date")
 
Terminated = CALCULATE(COUNTA(("your employments hire date"), USERELATIONSHIP('Calendar'[Date], ("employment termination date"))
 
Balance = [New Hires] - [Terminated]
 
Overall Current Employees = CALCULATE([Balance], FILTER (ALL('Calendar'[Date]), 'Calendar'[Date] <= MAX ('Calendar'[Date])))
 
P.S Use a generated calander date table to make a relansionship between the hire date and termination date, and you will be good to go 🙂

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

@BIHall 

 

Hi, 

 

Did you ever find a soultion for this, im struggeling to understand what the date in employee table actuly represents ?

 

 

 

 

Now Understood: The Date column in the Employee Table is used as follows:

 

In this dataset, once a month, an extract from the source system is produced. For each month that an employee is active they have a line of data on the first of the month as an employee “snapshot” fact table.

 

I read the "Data Warehouse Toolkit by Ralph Kimball and Margy Ross which has a small but useful section on thinking about models for HR and how to strucutre them.

 

powerbi hr file.PNG

Anonymous
Not applicable

Makes sense, cant belive I dident figure it out, its so obvious now, especially when looking at single emp ID's 🙂

 

Thanks @BIHall 

 

 

Anonymous
Not applicable

Yes, I'm not sure where I would get that date from my own data. I just have Hire/Termination date.

 

Edit: I see now, each employee line is duplicated for each month of their employment. Not sure how to duplicate this in my own table.

Anonymous
Not applicable

Hi @Anonymous 

 

I cant find the original post I personally found a soultion for, but this is what you can do, create these 4 messurements,

 

New Hires = COUNTA ("employment hire date")
 
Terminated = CALCULATE(COUNTA(("your employments hire date"), USERELATIONSHIP('Calendar'[Date], ("employment termination date"))
 
Balance = [New Hires] - [Terminated]
 
Overall Current Employees = CALCULATE([Balance], FILTER (ALL('Calendar'[Date]), 'Calendar'[Date] <= MAX ('Calendar'[Date])))
 
P.S Use a generated calander date table to make a relansionship between the hire date and termination date, and you will be good to go 🙂
v-chuncz-msft
Community Support
Community Support

@BIHall,

 

You may just disable the relationship to see the difference.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.