Forum Discussion
Find Employees in the Same Position with Multiple Employees Occupying the Position
Hi All,
I am trying to develop dax logic to identify those employees that have been in the same position for 6 weeks (42 days) where the position has multiple employees (incumbents) occupying it.
I want the logic to return 1 if all conditions are met as per the switch statement. I think I am almost there, but I think the issue is the many calculate functions are not working together somehow. Any advice appreciated. thanks.
IncumbentInSamePosition>6Weeks(WithMultipleIncumbents) =
VAR PosNum6Weeks =
CALCULATE(
CALCULATE (
MAX ( FACT_EmployeeDetails[Position Number] ),
DATEADD ( Dim_Date[Date], -42, DAY ),
ALLEXCEPT (
FACT_EmployeeDetails,
FACT_EmployeeDetails[Occupancy Date],
FACT_EmployeeDetails[Personnel Number]
)
),
FACT_EmployeeDetails[Employee Status] <> "Terminated",
)
VAR PosNumHasMultipleIncumbents =
CALCULATE (
COUNT ( FACT_EmployeeDetails[Personnel Number] ),
ALLEXCEPT (
FACT_EmployeeDetails,
FACT_EmployeeDetails[Occupancy Date],
FACT_EmployeeDetails[Position Number]
),
FACT_EmployeeDetails[Employee Status] <> "Terminated",
)
VAR CurrentPosition =
CALCULATE(
max( FACT_EmployeeDetails[Position Number] ),
ALLEXCEPT(
FACT_EmployeeDetails,
FACT_EmployeeDetails[Occupancy Date],
FACT_EmployeeDetails[Personnel Number]
),
FACT_EmployeeDetails[Employee Status] <> "Terminated",
)
RETURN
SWITCH( true(),
CurrentPosition = PosNum6Weeks // Position Number equals position number 6 weeks ago (42 days ago)
&&
PosNumHasMultipleIncumbents > 1 //AND Position has multiple incumbents
, 1
)
- Anonymous4 years ago
Managed to work it out myself.. thanks for your reply anyhow.
3 Replies
- AnonymousNot applicable
I think the reason this isn't working for me is because I am doing a calculation function = another calculate function and it's losing the context and therefore just returning the max of the first calculate and the max of the second calculate, which is not on the same row.
Anyone have any thoughts on this please?
- AnonymousNot applicable
Hi Anonymous ,
Your formula looks quite complicate.
The most direct method for you is to take out your variables separately and check which variable does not return the target value.
And the easiest way for us to help you to deal with the formua is you to share some sample data and expected here.
Best Regards,
Jay
- AnonymousNot applicable
Managed to work it out myself.. thanks for your reply anyhow.