Forum Discussion

Mosijoon's avatar
Mosijoon
Regular Visitor
3 years ago

Rephrasing the data

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.

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    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-power-bi

    • Mosijoon's avatar
      Mosijoon
      Regular Visitor
      3 years ago

      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?