The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I have a dataset of employee records. Each employee is one row; they each have columns for start / hire and end / exit dates, as well as some categorical characteristics like office location (one of five). There is a linked date table.
I have a calculated measure for ‘current employees’ that counts the employees who are employed in a given month accurately based on their hire date and exit date. But since this is counting rows or IDs it does not preserve other information from other columns. I’d like to count people by office division, for example, on a monthly basis. And ideally this could work with a slicer, e.g. a slicer selecting month, or even a date range, and a chart of employees by office location that dynamically updates.
Conceptually, what I imagine is basically a dynamic column of T or F values that updates dynamically for each row (employee) based on an input of a date, to indicate whether they worked there at that time. And then to be able to filter only the T rows and count by categories. I realize this may not be (I think is not) possible, but that is my mental model of it.
Is there a way of doing this without creating basically a new “current employees” count measure for each possible category?
Apologies if already answered elsewhere; I have not found a solution yet.
Solved! Go to Solution.
Use a disconnected dates table and try this measure:
Employee Count =
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER (
SUMMARIZE ( 'Table', 'Table'[Start Date], 'Table'[End Date] ),
'Table'[Start Date] <= MAX ( Dates[Date] )
&& (
'Table'[End Date] >= MIN ( Dates[Date] )
|| ISBLANK ( 'Table'[End Date] )
)
)
)
Hey @danextian @FreemanZ - thank you for your replies! I think the issue I am having compared to the examples shared above is the additional of a categorical variable that I want to count by.
A simplified and shortened version of the data for this example looks like this (it is fake data so not sensitive):
Employee ID | Start Date | End Date | Race |
7742 | 1/7/2014 | 9/8/2014 | White |
7799 | 1/17/2014 | Black | |
2884 | 1/26/2014 | 5/3/2014 | Asian |
1882 | 2/15/2014 | 11/5/2015 | Other |
4462 | 3/3/2014 | Other | |
9688 | 4/6/2014 | 12/8/2016 | White |
8871 | 4/10/2014 | Asian | |
3488 | 5/23/2014 | 9/14/2015 | Black |
6684 | 6/19/2014 | 12/9/2015 | Other |
4660 | 6/28/2015 | 12/25/2016 | Asian |
8071 | 8/14/2014 | 10/31/2016 | Black |
9557 | 7/26/2016 | 8/24/2016 | White |
We have a measure for current employees:
I know we could create a measure that counts people in each category, but that seems inefficient, and there are also multiple other categories. It would be great to be able to filter / slice the count across multiple categories. Is there a way to do this more seamlessly? Thanks!
Use a disconnected dates table and try this measure:
Employee Count =
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER (
SUMMARIZE ( 'Table', 'Table'[Start Date], 'Table'[End Date] ),
'Table'[Start Date] <= MAX ( Dates[Date] )
&& (
'Table'[End Date] >= MIN ( Dates[Date] )
|| ISBLANK ( 'Table'[End Date] )
)
)
)
Thank you @danextian - that worked! Although now I am feeling more confused than ever about date tables 😂
I thought linking a date table was necessary to provide a reference for the table of data, to ensure that all dates are represented. But using that date table to apply filter conditions does not work, I guess? So I also need a disconnected date table? Really appreciate the solution - would be curious about further context, perhaps if there's existing videos or articles covering this (I hadn't found any). Thanks!
You can still use a related dates table but you'll need to add a filter modifier. Assuming there is an active relationship between Dates[Date] and 'Table'[Start Date], your measure would be like this:
Employee Count =
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER (
ALL ( 'Table'[Start Date], 'Table'[End Date] ),
'Table'[Start Date] <= MAX ( Dates[Date] )
&& (
'Table'[End Date] >= MIN ( Dates[Date] )
|| ISBLANK ( 'Table'[End Date] )
)
),
REMOVEFILTERS ( Dates )
)
As always, please provide a sample data that can be copy pasted (not an image) and your expected result from that - it can be a link to an Excel file or a sanitized copy of your pbix. It is easier to provide a solution when there is data to work on.
User | Count |
---|---|
75 | |
70 | |
39 | |
30 | |
28 |
User | Count |
---|---|
104 | |
95 | |
51 | |
48 | |
46 |