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

[alexflo188]

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!

[d_rohlfs]

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. 

[d_rohlfs]

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)

[d_rohlfs]

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

[alexflo188]

I have thie error 😞

[alexflo188]

Hello @d_rohlfs 

Thanks for coming back quickly ! 

I have this error message in my visual

Thanks, 

[alexflo188]

@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 ?