Forum Discussion

Kaalbartje2's avatar
Kaalbartje2
Frequent Visitor
3 years ago

Count of total shift

Hi,

 

I have a datamodel with details when employ's are working.

A column with start (date and time) and end. A shift is mostly build of 3 rows

  1. Working part
  2. Break
  3. Working part.

I want to make something that I can count the shifts. Not the detail information but only the shifts. Sometimes it is enough tot set the values tot Unique but that doesnt work for a drill table. For the day overview it shows the correct value but when you zoom out tot the month (or week) overview it keeps counting the unique values and not the sum of total shifts.

 

So I have columns:

Start;End;employee number; department; shift code; activity type.

 

The last column is an code which says (with a number) what kind of activty it is (break = 4, working is 53 etc)

 

I hope it it's clear enough

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    Kaalbartje2 , Now very clear

     

    Count of shift can be

     

    Countrows(Summarize(Table, Table[Start], Table[End]))

     

    or

     

    Countrows(Summarize(filter(Table, Table[ activity type] <>  4) , Table[Start], Table[End]))

     

    or

     

     

    Countrows(Summarize(Table, Table[Start], Table[End], Table[Employee Number]))

    • Kaalbartje2's avatar
      Kaalbartje2
      Frequent Visitor
      3 years ago

      Hi,

       

      Tnx for the help. I'm trying what works best for me. I'll come back to it.

    • Kaalbartje2's avatar
      Kaalbartje2
      Frequent Visitor
      3 years ago

      Hi amitchandak ,

       

      So finaly had some time to play with your codes.

      Code: Countrows(Summarize(Table, Table[Date], Table[Employee Number]))

      gives almost the correct number of employees.

       

      The problem what I encounter is that we are working with nighshifts. The start on the fisrt day at 23:30 and end at day 2 at 07:00. Officialy they have a break. Let say around 02:00 till 02:30. And at 02:30 the second shift begins.

       

      When I use this code it counts the correct employees (start time 23:00 or 23:30) but also the employees who are also working the day before.

      I can filter that times in PowerBI but I was curious how it is possible to do this in a code.