Forum Discussion

friend_anand's avatar
friend_anand
New Member
6 years ago
Solved

How to capture corresponding manager ID based on transaction date if manager keeps changing

I have one table showing transaction date and employee ID.  And another table showing employee ID, start date and end date for each of the manager ID that he has been assigned to so far.   I want to ...
Need Help
  • vivran22's avatar
    vivran22
    6 years ago

    Hello friend_anand 

     

    You may use the following DAX post creating relationship between the two tables:

    ManagerID = 
CALCULATE (
    VALUES ( dtEmployee[MGR ID] ),
    FILTER (
        RELATEDTABLE ( dtEmployee ),
        dtTransaction[TRAN DATE] >= dtEmployee[START DATE]
            && dtTransaction[TRAN DATE]
                <= IF ( ISBLANK ( dtEmployee[END DATE] ), TODAY (), dtEmployee[END DATE] )
    )
)

     

    Regards,
    Vivek

    If it helps, please mark it as a solution

    Kudos would be a cherry on the top 🙂

    https://www.vivran.in/

  • vivran22's avatar
    vivran22
    6 years ago

    Hellofriend_anand 

     

    I have validated the formula with the conditions you have mentioned:

     

    For Manger 1, the date range is the same for Emp 1 & Emp 2

     

    EMP 1

     

    EMP 2

     

    Have you created the relationship between these two tables?

     

    In this case, it would be many-to-many with bi-directional filters

     

    Regards,

    Vivek

friend_anand's avatar
friend_anand
New Member
6 years ago

Hi Vivek,

I just notice that if the date range is same for 2 EMP ID's,  then it returns blank in the MGR ID.  

It should actually pick the corresponding MGR ID based on the EMP ID.  However, in the DAX you provided, I am not sure if it checks the EMP ID anywhere.  Please check and advise. 

Thanks, Anand

  • vivran22's avatar
    vivran22
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hellofriend_anand 

     

    I have validated the formula with the conditions you have mentioned:

     

    For Manger 1, the date range is the same for Emp 1 & Emp 2

     

    EMP 1

     

    EMP 2

     

    Have you created the relationship between these two tables?

     

    In this case, it would be many-to-many with bi-directional filters

     

    Regards,

    Vivek

    • friend_anand's avatar
      friend_anand
      New Member
      6 years ago

      Yes, it works now.. I think there was an issue in the relationship.. I set it right now.. 

      Thanks again, Vivek