Need help with a DAX measure (Headcount)

[alexflo188]

Hello everyone!

I'm excited to make my first post on this forum and to be a part of the Power BI community.

I've been exploring Power BI for about a year and am encountering a challenge with the following formula:

Headcount Status =
VAR SelectedDate = MAX('DateDimensionTable'[Date])
VAR LatestStatusPerEmployee =
CALCULATETABLE(
SUMMARIZE( /*to summarize by employee ID*/
'FactsTable',
'FactsTable'[EMPLOYEE_EMPLOYEEID],
"LatestStatusChange", MAX('FactsTable'[EMPLOYEEEMPLOYMENTSTATUS_CREATEDTIMESTAMP]), 
"LatestStatus", CALCULATE(MAX('FactsTable'[EMPLOYMENTSTATUS_LONGNAME]), /*is the column where the status are*/
USERELATIONSHIP('DateDimensionTable'[Date], 'FactsTable'[EMPLOYEEEMPLOYMENTSTATUS_CREATEDTIMESTAMP]),
FILTER(ALL('FactsTable'),
'FactsTable'[EMPLOYEE_EMPLOYEEID] = EARLIER('FactsTable'[EMPLOYEE_EMPLOYEEID])
&& 'FactsTable'[EMPLOYEEEMPLOYMENTSTATUS_CREATEDTIMESTAMP] == [LatestStatusChange]))
),
USERELATIONSHIP('DateDimensionTable'[Date], 'FactsTable'[EMPLOYEEEMPLOYMENTSTATUS_CREATEDTIMESTAMP])
)
VAR CountCondition =
FILTER(
LatestStatusPerEmployee,
[LatestStatus] IN {"Status 1", "status2", "status3"}
)
RETURN
COUNTROWS(CountCondition)

My objective is to create a dynamic headcount measure that tracks employee status changes over time. For instance, using a slicer, if I filter to December 31st, an employee who was in "Status 1" in January and moved to "Status 2" in December should be reflected correctly in the breakdown. However, I'm facing two main issues:

1. The overall headcount seems incorrect and tends to decrease as I go back in time, even though there should be variations.
2. In visualizations, employee statuses aren't updating correctly. For example, if an employee's status changed in August, that status remains fixed in all subsequent views.

In my model, the primary connection is between the date in my dimension table and the original hire date in the fact table. I've attempted to use a USERELATIONSHIP in the DAX to incorporate a link with the status creation timestamp, but this hasn't resolved the issue.

I'm open to any questions or suggestions for further clarification. Thank you in advance for your help!

[DallasBaba]

@alexflo188 you can try using the following DAX measure:

Headcount Status = 
VAR SelectedDate = MAX('DateDimensionTable'[Date])
VAR LatestStatusPerEmployee =
CALCULATETABLE(
    SUMMARIZE(
        'FactsTable',
        'FactsTable'[EMPLOYEE_EMPLOYEEID],
        "LatestStatusChange", MAX('FactsTable'[EMPLOYEEEMPLOYMENTSTATUS_CREATEDTIMESTAMP]), 
        "LatestStatus", CALCULATE(MAX('FactsTable'[EMPLOYMENTSTATUS_LONGNAME]),
            USERELATIONSHIP('DateDimensionTable'[Date], 'FactsTable'[EMPLOYEEEMPLOYMENTSTATUS_CREATEDTIMESTAMP])
        )
    ),
    USERELATIONSHIP('DateDimensionTable'[Date], 'FactsTable'[EMPLOYEEEMPLOYMENTSTATUS_CREATEDTIMESTAMP])
)
VAR CountCondition =
FILTER(
    LatestStatusPerEmployee,
    [LatestStatus] IN {"Status 1", "status2", "status3"}
)
RETURN
COUNTROWS(CountCondition)

This measure calculates the headcount based on the latest status change for each employee. It also filters the results to only include employees with “Status 1”, “status2”, or “status3”. You can use this measure in a PivotTable to display the headcount by date.

Here are the steps to create a disconnected table:

1. Create a new table with a single column that contains all the dates you want to filter by.
2. Create a relationship between the new table and the DateDimensionTable.
3. Use the new table as a slicer in your visualizations.

By using a disconnected table, you can filter the data by date without affecting the overall headcount.

Let me know if this works for you. @ me in replies, or I'll lose your thread!!!  
Note:

If this post is helpful, please mark it as the solution to help others find it easily. Also, if my answers contribute to a solution, show your appreciation by giving it a thumbs up! 

[alexflo188]

Hello @DallasBaba, 

Thanks for you suggestion. That being said, could you explain me why I should use the expression USERELATIONSHIP in the measure if I'm not using the connection from the datedimension table and primary fact table? 

Thanks in advance ! 😄 

Alex,