Rephrasing the data

[Mosijoon]

Hello everyone

Sorry, I couldn't paste the actual table. my data looks like this:

and I'd like to rephrase it to this:

I tried Unpivote in power query and managed to get the Employee and Serial right but for getting the values for Task1_hour and Task2_hour I tried merging the main table and the unpivoted table on Serial and Employee=Task1_employee but it gives me the redundant value for the employees that are common in those two columns. so I get this:

Thanks in advance for your help.

[amitchandak]

@Mosijoon , Create a common dimension employee

Employee= distinct(union(distinct(Table1[Employee]),distinct(Table1[Task2_Employee])))

Then join with both employee and Task2_Employee , assume join with Task2_Employee is inactive

M1=  Sum(Table1[Task1_hour])

M2= calculate(Sum(Table1[Task1_hour]) , userelationship(Employee[Employee], Table1[Task2_Employee]) )

you can use these two measures with common employee dim and  other columns

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...

[Mosijoon]

Thank you for your reply. It works when you only want the total sum value but I still have a problem with slicers and tables. it only brings employees from the active relationship (to select in the slicer or as the first column in tables). is there a way around that? something like userelationship for slicers?