Seeking DAX Help for Unique Employee Headcount Over Time with Dynamic Status Filtering
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!
