Report on Project Assignment - Multi-relationship problem

[ACI]

Hi,

I'm struggling to solve a relationship problem between two tables.

Consider this scenario:

We have a simple project table with employees assigned to the different project roles. Example attached.

The end goal is to provide employee availability visual to the upper management.

Of course I cannot create multiple relationships between master Name Table, and different columns on my Project table, but I don’t know what the work around would be (combining columns will not allow me to report based on the individual employee, so this type of solution is not viable https://radacad.com/relationship-in-power-bi-with-multiple-columns).

[v-easonf-msft]

Hi , @ACI 

Could you please tell me whether your problem has been solved?
If it is,  please mark the helpful replies or add your reply as Answered to close this thread?

Best Regards,
Community Support Team _ Eason

[amitchandak]

There should be a project end date to know when is employee getting free.

If yes, refer this blog to see how to deal with two dates:https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Change the current employee calc to look outside the range to known when the employee is available

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin

[ACI]

Hi @amitchandak, I'm still struggeling.

I do have a end date, but unlike in your case, my goal is not counting the number of employees, but assigning the availability date to each employee.

What complicates the situation is the fact that my employee can be assigned various roles, and various projects simultaneously.

I was trying to follow https://community.powerbi.com/t5/Community-Blog/Good-Ol-VLOOKUP-The-Ultimate-Guide-to-Lookups-in-Pow..., but cannot cope with multipe columns (various project roles). 

[ACI]

Thank you @amitchandak , I'm following your steps, will let you know if I solve my case.