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 headcount data which includes the period, employee ID, employee name, job title, manager ID, and manager name.
I'm trying to create a visual where if an employee ID is filtered in Table 1, it will show me that employee's direct reports (those employees that have that employee ID as their Manager ID) in Table 2. I'm also trying to add a second filter on Table 2 to try to toggle between the periods. However, I for the life of me can't figure this out.
Below is an overview of the data set:
Period | Employee ID | Name | Job Title | Manager ID | Manager Name |
Jan-22 | 001 | Employee 1 | CEO | N/A | N/A |
Jan-22 | 002 | Employee 2 | Director | 001 | Employee 1 |
Jan-22 | 003 | Employee 3 | Senior Analyst | 002 | Employee 1 |
Jan-22 | 004 | Employee 4 | Senior Analyst | 002 | Employee 1 |
Jan-22 | 005 | Employee 5 | Analyst | 002 | Employee 1 |
Feb-22 | 001 | Employee 1 | CEO | N/A | N/A |
Feb-22 | 002 | Employee 2 | Director | 001 | Employee 1 |
Feb-22 | 003 | Employee 3 | Senior Analyst | 002 | Employee 2 |
Feb-22 | 004 | Employee 4 | Senior Analyst | 002 | Employee 2 |
Feb-22 | 005 | Employee 5 | Senior Analyst | 003 | Employee 2 |
Table 1
Period | Employee ID | Name | Job Title | Manager ID | Manager Name |
Jan-22 | 001 | Employee 1 | CEO | N/A | N/A |
Feb-22 | 001 | Employee 1 | CEO | N/A | N/A |
Table 2 (Ideal scenario)
Period | Employee ID | Name | Job Title | Manager ID | Manager Name |
Jan-22 | 002 | Employee 2 | Director | 001 | Employee 1 |
Jan-22 | 003 | Employee 3 | Senior Analyst | 002 | Employee 1 |
Jan-22 | 004 | Employee 4 | Senior Analyst | 002 | Employee 1 |
Jan-22 | 005 | Employee 5 | Analyst | 002 | Employee 1 |
Feb-22 | 002 | Employee 2 | Director | 001 | Employee 1 |
Unfortunately, there is something that is preventing me from creating a relationship between Employee ID and Manager ID.
Solved! Go to Solution.
Hi @akhreis
You can create a same table, but it does not have relationship with the original table.
then use the new table as table 1, the original table as table 2
then create a measure in original table"
Measure = IF(ISFILTERED('Table (2)'[Employee ID])=FALSE(),1,IF(MAX('Table'[Manager Name])=MAX('Table (2)'[Name]),1,0))
put the measure to the visual filter of table2
Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @akhreis
You can create a same table, but it does not have relationship with the original table.
then use the new table as table 1, the original table as table 2
then create a measure in original table"
Measure = IF(ISFILTERED('Table (2)'[Employee ID])=FALSE(),1,IF(MAX('Table'[Manager Name])=MAX('Table (2)'[Name]),1,0))
put the measure to the visual filter of table2
Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Woohoo! Perfect!
Hello @akhreis ,
The try doing the relationship between employee ID from table 1 to employee ID from table 2.
If I answered your question, please mark my post as solution, Appreciate your Kudos👍
Proud to be a Super User! | |
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 |
---|---|
113 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |