topic Re: Seeking DAX Help for Unique Employee Headcount Over Time with Dynamic Status Filtering in DAX Commands and Tips

Seeking DAX Help for Unique Employee Headcount Over Time with Dynamic Status Filtering

alexflo188 — Thu, 01 Feb 2024 14:02:06 GMT

Hello Power BI Enthusiasts!

I trust you're all doing wonderfully.

I'm in a bit of a bind with a DAX measure I'm trying to construct in Power BI, and I could really use your collective expertise. My goal is to create a measure that counts unique employee records based on their employee number and filters these counts by their employment status using a related status dimension. This measure is intended to be dynamic, reflecting changes over time, leveraging a unique timestamp that marks each status change for the employees.

Despite my efforts, I'm encountering an issue with duplicates in the data,

Here is the DAX I'm using

Test_Headcount_Dimstatus =
VAR __selecteddate = MAX(Dim_calendar_start[Date])
VAR LatestEmployeeRecord =
SUMMARIZE(
'EMPLOYEES - Ceridian modified',
'EMPLOYEES - Ceridian modified'[EMPLOYEE_EMPLOYEEID],
"LatestTimestamp", MAX('EMPLOYEES - Ceridian modified'[EMPLOYEEEMPLOYMENTSTATUS_CREATEDTIMESTAMP])
)
VAR FilteredEmployeeRecord =
FILTER(
LatestEmployeeRecord,
[LatestTimestamp] <= __selecteddate
)
RETURN
COUNTROWS(FilteredEmployeeRecord)

Accompanying this post is a snapshot of my model for reference.

I've been grappling with this problem for over a month, and it's quite frustrating. Does anyone have any insights into what might be going wrong? Any suggestions or guidance would be greatly appreciated.

Thank you for taking the time to help!

Re: Seeking DAX Help for Unique Employee Headcount Over Time with Dynamic Status Filtering

d_rohlfs — Wed, 31 Jan 2024 19:25:09 GMT

Hey AlexFlo188,

Try this:
Test_Headcount_Dimstatus 2 =
VAR _SelectedDate = MAX(Dim_calendar_start[Date])
VAR _LatestEmployeeRecord =
SummarizeColumns(
'EMPLOYEES - Ceridian modified'[EMPLOYEE_EMPLOYEEID],
"LatestTimestamp", MAX('EMPLOYEES - Ceridian modified'[EMPLOYEEEMPLOYMENTSTATUS_CREATEDTIMESTAMP])
)
VAR FilteredEmployeeRecord =
FILTER(
_LatestEmployeeRecord,
[LatestTimestamp] <= _SelectedDate
)
VAR _Result =
COUNTROWS(
FilteredEmployeeRecord
)
RETURN
_Result

Re: Seeking DAX Help for Unique Employee Headcount Over Time with Dynamic Status Filtering

alexflo188 — Wed, 31 Jan 2024 19:45:47 GMT

Hello d_rohlfs

Thanks for coming back quickly !

I have this error message in my visual

Thanks,

Re: Seeking DAX Help for Unique Employee Headcount Over Time with Dynamic Status Filtering

d_rohlfs — Wed, 31 Jan 2024 20:37:41 GMT

Hey alexflo188 ,
Try just going back to your original equation and instead of doing a Countrows for your result do DISTINCTCOUNT('FilteredEmployeeRecord'[EMPLOYEE_EMPLOYEEID])

This won't fix the table that you are making, but it should get your count correct.

Re: Seeking DAX Help for Unique Employee Headcount Over Time with Dynamic Status Filtering

d_rohlfs — Wed, 31 Jan 2024 20:43:15 GMT

Or you could try:

Test_Headcount_Dimstatus =
VAR __selecteddate = MAX(Dim_calendar_start[Date])
VAR LatestEmployeeRecord =
SUMMARIZE(
'EMPLOYEES - Ceridian modified',
'EMPLOYEES - Ceridian modified'[EMPLOYEE_EMPLOYEEID],
"LatestTimestamp", MAX('EMPLOYEES - Ceridian modified'[EMPLOYEEEMPLOYMENTSTATUS_CREATEDTIMESTAMP])
)
VAR FilteredEmployeeRecord1=
FILTER(
LatestEmployeeRecord,
[LatestTimestamp] <= __selecteddate
)
VAR FilteredEmployeeRecord =
FILTER(
FilteredEmployeeRecord1,
DISTINCT(
[EMPLOYEE_EMPLOYEEID])
)
RETURN
COUNTROWS(FilteredEmployeeRecord)

Re: Seeking DAX Help for Unique Employee Headcount Over Time with Dynamic Status Filtering

alexflo188 — Wed, 31 Jan 2024 20:48:46 GMT

I have thie error 😞

Re: Seeking DAX Help for Unique Employee Headcount Over Time with Dynamic Status Filtering

alexflo188 — Thu, 01 Feb 2024 14:01:02 GMT

d_rohlfs I've read on window fonction and I think that might be somthing that work. However I'm not entirely sure how that works could you help me with this ?