Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 DATE | EMP ID | EMP ID | MGR ID | START DATE | END DATE | |
3/25/2014 | E00001 | E00001 | M00001 | 4/1/2011 | 3/31/2016 | |
2/18/2015 | E00001 | E00001 | M00002 | 4/1/2016 | 7/31/2019 | |
10/12/2015 | E00001 | E00001 | M00003 | 8/1/2019 | ||
4/8/2016 | E00001 | E00002 | M00001 | 4/1/2011 | 3/31/2017 | |
9/15/2016 | E00001 | E00002 | M00002 | 4/1/2017 | 1/31/2020 | |
2/16/2017 | E00001 | E00002 | M00003 | 2/1/2020 | ||
11/20/2017 | E00001 | E00003 | M00004 | 4/1/2011 | 12/31/2015 | |
5/6/2018 | E00001 | E00003 | M00005 | 1/1/2016 | ||
12/9/2018 | E00001 | |||||
3/9/2019 | E00001 | |||||
7/31/2019 | E00001 | |||||
8/1/2019 | E00001 | |||||
12/15/2019 | E00001 | |||||
2/20/2020 | E00001 | |||||
3/30/2014 | E00002 | |||||
2/23/2015 | E00002 | |||||
10/17/2015 | E00002 | |||||
4/13/2016 | E00002 | |||||
9/20/2016 | E00002 | |||||
2/21/2017 | E00002 | |||||
11/25/2017 | E00002 | |||||
5/11/2018 | E00002 | |||||
12/14/2018 | E00002 | |||||
3/14/2019 | E00002 | |||||
8/5/2019 | E00002 | |||||
8/6/2019 | E00002 | |||||
12/20/2019 | E00002 | |||||
2/25/2020 | E00002 | |||||
4/4/2014 | E00003 | |||||
2/28/2015 | E00003 | |||||
10/22/2015 | E00003 | |||||
4/18/2016 | E00003 | |||||
9/25/2016 | E00003 | |||||
2/26/2017 | E00003 | |||||
11/30/2017 | E00003 | |||||
5/16/2018 | E00003 | |||||
12/19/2018 | E00003 | |||||
3/19/2019 | E00003 | |||||
8/10/2019 | E00003 | |||||
8/11/2019 | E00003 | |||||
12/25/2019 | E00003 | |||||
3/1/2020 | E00003 |
Solved! Go to Solution.
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/
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
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
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
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
Yes, it works now.. I think there was an issue in the relationship.. I set it right now..
Thanks again, Vivek
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 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |