Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I'd like to count the number of active employees in a certain period.
It looks like I have a little bit of work to do because the calculated measure I currently use doesn't really work that well.
I have a table with all employee assignments. Each employee can have multiple assignments and sometime those assignments might overlap.
I have a start and end date for each of these assignments and I also have a Date Table that is linked to start date of the employee assignments table.
1. How can I count the active number of employees and account for the fact that an employee might have multiple assignments? If I count distinct wouldn't that ignore the other assignments that the employee has?
2. The formula I currently doesn't work correctly when I filter the table by activity type for example, it keeps the same ratio when applying filters which is obviously not correct. I'm new to Power BI, I'm assuming this is because I have to create a Measure ahead of time for each filter I might use and don't allow the user to change the filters?
Here's the current formula:
Here's a sample table:
Employee Name | Start Date | End Date | ActivityType |
Ben | 12/20/2019 | 12/22/2019 | Project |
Ben | 12/21/2019 | 12/25/2019 | Project |
Ben | 12/22/2019 | 12/24/2019 | Project |
Steven | 12/21/2019 | 12/22/2019 | Project |
Mike | 12/21/2019 | 12/23/2019 | Project |
Mike | 12/27/2019 | 12/28/2019 | Travel |
Tim | 12/26/2019 | 12/26/2019 | Travel |
Tim | 12/14/2019 | 12/17/2019 | Project |
Expected result for December: 4
Expected result for next week (this week ends sunday the 22nd): 3 (Ben, Mike, Tim)
Expected result for next week when filtering by Project - 2 (Ben, Mike)
Solved! Go to Solution.
Hey @WorkHard ,
here you will find a solutions that provides a solution to your challenge:
The challenge you are facing has a name: event-in-progress.
There are a lot of blog posts about this problem available. Please be aware that my solution follows a different approach, as I expand the table that contains the start and end date in a way that the new table also has a single date column, in my solution I use this DAX statement to expand the base table:
EmployeesActivityDays =
GENERATE(
'EmployeesActivity'
, var _DateStart = 'EmployeesActivity'[Start Date]
var _DateEnd = 'EmployeesActivity'[End Date]
return
GENERATESERIES(_DateStart , _DateEnd , 1)
)
Please be aware that I added a column that allows to differentiate the activities.
The above approach allows to leverage the power of filter propagation by relating the calendar table to the date column of the new table.
There is a very simple measure:
No Of Active Employees =
DISTINCTCOUNT('EmployeesActivityDays'[Employee Name])
This allows to create two table visuals like so:
Don't be afraid of expanding the table and creating a table with hundreds of thousands to tenth of millions of rows, basically scanning a simple table always performs much better than evaluating "compex" formulas, where different columns are combined forcing query materialization.
Hopefully this provides what you are looking for
Regards,
Tom
Hey @TomMartens
I came across your awesome solution to this problem, it works perfectly in my model, thanks! I have one follow-up question though.
Your measure counts the amount of employees that were active at any date in the period in the visual. But in my model, I need to know which (in my case, projects) were active at the end of the period.
Any tips on how to work that into the measure?
Hey @Bertverderrie ,
this measure counts the active ... at the end of the period (the last date in the period):
No Of Active Employees (End of Priod) =
var __maxPeriod = MAXX( VALUES( 'calendar'[Date] ) , 'calendar'[Date] )
return
CALCULATE(
DISTINCTCOUNT('EmployeesActivityDays'[Employee Name])
, 'calendar'[Date] = __maxPeriod
)
Hopefully, this provides what you are looking for.
If not, please create a pbix that contains sample data but still reflects your data model. Upload the pbix to onedrive or dropbox and share the link. If you are using Excel to create the sample data, share the xlsx as well and please start a new question. This will avoid confusion about the topic of this question.
Regards,
Tom
HI,
I want to find the active manager with designation of manager and Sr. Manager.
Hi,
You may download my PBI file from here.
Hope this helps.
Hey @WorkHard ,
here you will find a solutions that provides a solution to your challenge:
The challenge you are facing has a name: event-in-progress.
There are a lot of blog posts about this problem available. Please be aware that my solution follows a different approach, as I expand the table that contains the start and end date in a way that the new table also has a single date column, in my solution I use this DAX statement to expand the base table:
EmployeesActivityDays =
GENERATE(
'EmployeesActivity'
, var _DateStart = 'EmployeesActivity'[Start Date]
var _DateEnd = 'EmployeesActivity'[End Date]
return
GENERATESERIES(_DateStart , _DateEnd , 1)
)
Please be aware that I added a column that allows to differentiate the activities.
The above approach allows to leverage the power of filter propagation by relating the calendar table to the date column of the new table.
There is a very simple measure:
No Of Active Employees =
DISTINCTCOUNT('EmployeesActivityDays'[Employee Name])
This allows to create two table visuals like so:
Don't be afraid of expanding the table and creating a table with hundreds of thousands to tenth of millions of rows, basically scanning a simple table always performs much better than evaluating "compex" formulas, where different columns are combined forcing query materialization.
Hopefully this provides what you are looking for
Regards,
Tom
That is a very cool approach @TomMartens , thanks for sharing!
Proud to be a Super User!
Hey @JarroVGIT ,
thanks.
My pleasure,
Tom
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |