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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Employees headcount

Hello All, 

 

I am working on power bi and I am facing a challenge in calculating total number of employees, 

I have seen several articals in which they create inactive relationship between calendar and employee table. That works.

But my problem is I want to filter the data too and I have several other tables.

Employee table - Emplyee ID, start date and end date (if present else blank)

Timesheet table - This is my transaction table and I have several other tables as well like cost centre.

So, Timesheet is my transaction table and employee table is master's table.

Please do suggest me some other methods to make it work.

 

Below are the DAX which is used and they worked but for inactive relationship of employee and calendar table.

 
 
Headcount=CALCULATE(COUNTROWS(Employee),FILTER(VALUES(Employee[End_Date]),ISBLANK(Employee[End_Date])),FILTER(VALUES(Employee),[Start_date]<=MAX('Calendar'[Date])))
 
 
 
Eployee Joined =
var a = YEAR(MAX('Calendar'[Date]))
var b = MONTH(MAX('Calendar'[Date]))
return
CALCULATE(COUNTROWS('Employee'),FILTER(VALUES('Employee'[Start_date]),YEAR('Employee'[Start_date])=a && MONTH('Employee'[Start_date])=b))
 
 
 
Employee Left = var a = YEAR(MIN('Calendar'[Date]))
var b = MONTH(MIN('Calendar'[Date]))
return
CALCULATE(COUNTROWS('Employee'),FILTER(VALUES('Employee'[End_Date]),YEAR('Employee'[End_Date])=a && MONTH('Employee'[End_Date])=b))
 
 

 

8 REPLIES 8
Mariusz
Community Champion
Community Champion

Hi @Anonymous 

 

Try expanding your dates like in the video below, also see the attached for reference.

 

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn


 

amitchandak
Super User
Super User
Anonymous
Not applicable

Actual RelationsActual Relations

 

 

Left Side actual result, right side by using your DAX measuresLeft Side actual result, right side by using your DAX measures

 

 

Hello @amitchandak 

Thankyou for your reply, I have attached 2 images, the first one shows the actual relations which i made and second one shows the results, 

Results in left table are the actual results and right ones i created by using the dax which you shared.

Please have a look what I atually want, and do suggest 

Thanks!

@Anonymous , can you share the formula you used. Do these dates have a timestamp. hire number seems off by far at few places.

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Anonymous
Not applicable

Hello, I have basically 3 tables, Calendar Table in which i have dates from 1/1/2019 to 12/31/2020 Timehseet table in which I have timesheet data, suppose I have 1000 employees and in a particular week only 800 employees have filled time sheet so it will have data for just 800 emplyee for latest week.
and last i have employee table in which i have all employee information, date of joining and date of termination share.PNG

 

i made an active relation with calender's date to timesheet date
and another active relation of timesheet employee id with employee's table employee id

and 2 inactive relation of emplyee and calendar table on start date and end date with calendar date

 

and i am using these measures 

 

Headcount = CALCULATE(COUNTROWS('Employee count'),FILTER(VALUES('Employee count'[End_Date]),OR(ISBLANK('Employee count'[End_Date]),'Employee count'[End_Date]>=MIN('Calendar'[Date]))),FILTER(VALUES('Employee count'),[Start_date]<=MAX('Calendar'[Date])))
 
 
 
Employee Left = var a = YEAR(MIN('Calendar'[Date]))
var b = MONTH(MIN('Calendar'[Date]))
return
CALCULATE(COUNTROWS('Employee count'),FILTER(VALUES('Employee count'[End_Date]),YEAR('Employee count'[End_Date])=a && MONTH('Employee count'[End_Date])=b))
 
 
 
Eployee Joined =
var a = YEAR(MAX('Calendar'[Date]))
var b = MONTH(MAX('Calendar'[Date]))
return
CALCULATE(COUNTROWS('Employee count'),FILTER(VALUES('Employee count'[Start_date]),YEAR('Employee count'[Start_date])=a && MONTH('Employee count'[Start_date])=b))
 
 
the result of these dax measure is not coming okay because these measures are following the active path to filter data which is calendar-timesheet-employee, but i want the calendar to communicate directly with employee.  
 
Can you please help me out I am stuck in this.
 
result.PNG
 
This is the actual result. 
 
Please help me out.
Thanks in advance.
Anonymous
Not applicable

Hello @amitchandak 

 

What I did is as I showed my model looks like that, and I used the formula which you shared for inactive relationships and the result comes out to be the one on right hand side.

 

Employee Table has ID, Start date and End date

Calendar table has Date, year, month, period

 

And in my model I have joined calendar table with timesheet table on date basis and 

Joined timesheet table with employee table on ID basis

when I make 2 inactive relation with calendar and employee table. Then results comes out to be as I attached earlier.

 Basically timesheet is my transaction table and employee, Workorder, costcentre tables are my fact table.

All tables are directly joined with timesheet table.

 

Sorry, I can not share the data as its very sensitive. Can you prepare a model and work on it by taking random values.

Thanks! 

 

 

 

Hi, @Anonymous 

Have  you tried the funtion of "userelationship"  when you need use  an inactive relationship?

 

Here is a simple sample.

pbix attached

Hope it will help you.

 

Best Regards,
Community Support Team _ Eason

Anonymous
Not applicable

Yes, I have tried that too, nothing worked

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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