Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
friend_anand
New Member

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 get the corresponding manager ID based on the transaction date in my first table.  Below are the tables:

 

Note: If end date is blank, then that manager ID applies until current date.

 

Table 1: Transaction Table Table 2: Emp_Manager Table
TRAN DATEEMP ID EMP IDMGR IDSTART DATEEND DATE
3/25/2014E00001 E00001M000014/1/20113/31/2016
2/18/2015E00001 E00001M000024/1/20167/31/2019
10/12/2015E00001 E00001M000038/1/2019 
4/8/2016E00001 E00002M000014/1/20113/31/2017
9/15/2016E00001 E00002M000024/1/20171/31/2020
2/16/2017E00001 E00002M000032/1/2020 
11/20/2017E00001 E00003M000044/1/201112/31/2015
5/6/2018E00001 E00003M000051/1/2016 
12/9/2018E00001     
3/9/2019E00001     
7/31/2019E00001     
8/1/2019E00001     
12/15/2019E00001     
2/20/2020E00001     
3/30/2014E00002     
2/23/2015E00002     
10/17/2015E00002     
4/13/2016E00002     
9/20/2016E00002     
2/21/2017E00002     
11/25/2017E00002     
5/11/2018E00002     
12/14/2018E00002     
3/14/2019E00002     
8/5/2019E00002     
8/6/2019E00002     
12/20/2019E00002     
2/25/2020E00002     
4/4/2014E00003     
2/28/2015E00003     
10/22/2015E00003     
4/18/2016E00003     
9/25/2016E00003     
2/26/2017E00003     
11/30/2017E00003     
5/16/2018E00003     
12/19/2018E00003     
3/19/2019E00003     
8/10/2019E00003     
8/11/2019E00003     
12/25/2019E00003     
3/1/2020E00003     
2 ACCEPTED SOLUTIONS
vivran22
Community Champion
Community Champion

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/

View solution in original post

Hello@friend_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

Capture2.JPG

 

EMP 2

Capture1.JPG

 

Have you created the relationship between these two tables?

 

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

3.png

 

Regards,

Vivek

View solution in original post

5 REPLIES 5
friend_anand
New Member

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

Hello@friend_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

Capture2.JPG

 

EMP 2

Capture1.JPG

 

Have you created the relationship between these two tables?

 

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

3.png

 

Regards,

Vivek

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

Thanks again, Vivek

vivran22
Community Champion
Community Champion

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/

Thank you, Vivek.

It works for me 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.