I have a projects table which includes a "technical associate" and a "sales associate" which are employees. I also have a table containing all employees, some of which may not be on the projects table. My goal is to have a slicer that allows interactivity by selecting employee names and displaying or counting projects for which they are either a technical or sales associate because some individuals fulfill both roles for different projects.
I've envisioned having two slicers so that you could select one technical/sales associate in one slicer and the list of options in the second (which would be sales/technical, respectively) would narrow down to just the list of employees who have participated as an associate with the first employee on at least one project. My first attempt was to relate the employee table to the projects table by mapping employee name to the technical associate and sales associate columns, but PowerBI does not seem to support relationships to multiple columns in this manner. I tried using calculated tables, but those appear to only be updated at refresh time and so are ill-suited for this task. Finally, I tried duplicating the employee table and relating one to technical associate and one to sales associate, but unfortunately this does not work either because I cannot also have bidrectional cross-filtering across the two employee tables or through the projects table.
What would be the proper way to approach this problem? Thanks.
