Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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
)

 

  • Anonymous's avatar
    Anonymous
    4 years ago

    Managed to work it out myself..  thanks for your reply anyhow.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not 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?

  • Anonymous's avatar
    Anonymous
    Not 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

    • Anonymous's avatar
      Anonymous
      Not applicable

      Managed to work it out myself..  thanks for your reply anyhow.