Forum Discussion

Billy_1979's avatar
Billy_1979
Frequent Visitor
2 years ago
Solved

help with different employee shift

Hi,  Im new to power bi. i was wondering if you can help i have a table for working  pattern for employees   i trying to work out how long they have worked on a project i have managed to wo...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Billy_1979 

     

    Do you link the 'Task Duration' table to 'Calendar' table on Employee column? From the current data, it will be a many-to-many relationship: 'Task Duration'[assignee]  -- 'Calendar'[Employee]

     

    If building above relationship doesn't make the formula work correctly, remove this relationship and try this formula instead:

    03- Hours spent =
VAR _Start = 'Task Duration'[firstDateMovedTo_Design WIP]
Var _End = 'Task Duration'[lastDateMovedOutOf_Design WIP]
Var _assignee = 'Task Duration'[assignee]
Return SUMX(
            CALCULATETABLE(
                    'Calendar',
                    DATESBETWEEN('Calendar'[Date],_Start,_End),
                    'Calendar'[Workday1] = 1,
                    'Calendar'[Employee] = _assignee
            ),
            MAX(MIN('Calendar'[End],_End) - MAX('Calendar'[Start],_Start),0) * 24

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Sorry I thought you were creating a measure. I didn't realize this was a calculated column. How about try your original formula with the workday1 column?

03- Hours spent =
VAR _Start = 'Task Duration'[firstDateMovedTo_Design WIP]
Var _End = 'Task Duration'[lastDateMovedOutOf_Design WIP]
Return SUMX(
            CALCULATETABLE(
                    'Calendar',
                    DATESBETWEEN('Calendar'[Date],_Start,_End),
                    'Calendar'[Workday1] = 1
            ),
            MAX(MIN('Calendar'[End],_End) - MAX('Calendar'[Start],_Start),0) * 24

 

Billy_1979's avatar
Billy_1979
Frequent Visitor
2 years ago

Hi thanks for the reply- 

i tried that yesterday and as i said its looks like its bringing back everyone who as worked that day rather than the particalur employee assigned - 

 

 

how would i assign a employee for each project.

 

Thanks.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Billy_1979 

     

    Do you link the 'Task Duration' table to 'Calendar' table on Employee column? From the current data, it will be a many-to-many relationship: 'Task Duration'[assignee]  -- 'Calendar'[Employee]

     

    If building above relationship doesn't make the formula work correctly, remove this relationship and try this formula instead:

    03- Hours spent =
VAR _Start = 'Task Duration'[firstDateMovedTo_Design WIP]
Var _End = 'Task Duration'[lastDateMovedOutOf_Design WIP]
Var _assignee = 'Task Duration'[assignee]
Return SUMX(
            CALCULATETABLE(
                    'Calendar',
                    DATESBETWEEN('Calendar'[Date],_Start,_End),
                    'Calendar'[Workday1] = 1,
                    'Calendar'[Employee] = _assignee
            ),
            MAX(MIN('Calendar'[End],_End) - MAX('Calendar'[Start],_Start),0) * 24

     

  • Billy_1979's avatar
    Billy_1979
    Frequent Visitor
    2 years ago

    Thanks for your help with this query it worked perfectly.

     

    Thanks again.