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 named "key employees" with employee names. I have two one-to-many relationships from the employee table (tblLeaveMaster and Masterall). My problem is that my Masterall table has a "active?" column that I need to use to filter my tblLeaveMaster. I basically want to filter out non-active employees.
I think that all I need is a calculated column in my tblLeaveMaster that checks the [employee name] against masterall[employee name] and then sees if masterall[active?] has a 1. It seems like the relationship is there in the view, but doesn't actually work in the visuals. Any help is appreciated.
Solved! Go to Solution.
Hi @joshcomputer1,
Since you have established one to many relationship for both 'tblLeaveMaster' and 'Masterall', there existing a indirect many to many relationship between them. It is not possible to add fields from these two tables in a single visual because it can't determine the mapping relationship.
To check the [employee name] in 'tblLeaveMaster' against MasterAll[employee name], please create a calculated column in 'Key Employee' table.
Active = LOOKUPVALUE(MasterAll[Active],MasterAll[Employee],'Key Employee'[Employee])
Then, you can drag 'tblLeaveMaster'[Employee Name] and 'Key Employee'[Active] into the same table visual.
Best regards,
Yuliana Gu
Hi @joshcomputer1,
Since you have established one to many relationship for both 'tblLeaveMaster' and 'Masterall', there existing a indirect many to many relationship between them. It is not possible to add fields from these two tables in a single visual because it can't determine the mapping relationship.
To check the [employee name] in 'tblLeaveMaster' against MasterAll[employee name], please create a calculated column in 'Key Employee' table.
Active = LOOKUPVALUE(MasterAll[Active],MasterAll[Employee],'Key Employee'[Employee])
Then, you can drag 'tblLeaveMaster'[Employee Name] and 'Key Employee'[Active] into the same table visual.
Best regards,
Yuliana Gu
Not sure if this is best practice, but I merged the employee key with masterall. This gets me the list of active employees with their most recent stint on a team. I replaced the employee key with the merge.
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |