Forum Discussion

mouzzampk's avatar
mouzzampk
Helper I
2 years ago

Count Serial if Outgoing Reps Time is between Sign In Start and Finish Time

Hi,  Both tables have relationship and I am looking for a way "Count Serial if Outgoing Reps Time is between Sign In Start and Finish Time" I am trying to create a New Measure. I have done this so ...
General Comment
Need Help
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi mouzzampk ,

Consider publishing a simple file (without private data) with the expected output, that would be helpful, thanks.
How to provide sample data in the Power BI Forum

Best Regards,
Gao
Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

  • mouzzampk's avatar
    mouzzampk
    Helper I
    2 years ago

    Apologies for not providing enough information.

     

    Sign In Sheet

     

    Date                  Officer    Start         Finish        O/T Start    O/T Finish

    23/10/2023  User 1    08:50:00  15:00:00  04:30:00  06:30:00
    26/10/2023  User 2  08:00:00  16:00:00  06:45:00  07:45:00
    26/10/2023  User 3    16:00:00  18:00:00
    27/10/2023  User 4  08:00:00  16:05:00  06:45:00  07:45:00

     

    Workflow Sheet

     

    Serial              Date             Time         Officer

    EA13243243  02/03/2023  07:08:00  User 1
    EA13243244  05/03/2023  21:40:00  User 1
    EA13243245  05/03/2023  07:12:00  User 2
    EA13243246  05/03/2023  15:26:00  User 3
    EA13243247  09/03/2023  13:05:00

      User 3

    EA13243248  09/03/2023  13:50:00  User 4

     

    I am looking for DAX functon to Count number of Serial recorded by Officers during Normal Hours and O/T Hours from Sign In Sheet.

     

    Expected Outcome

     

    Normal Hours            O/T Hours

    20                               15

     

    I hope this gives more information.

     

    Thank you

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi mouzzampk ,

      Check if this returns the expected value:

      NormalHours = 
CALCULATE(
    COUNT('Workflow Sheet'[Serial]),
    FILTER(
        'Workflow Sheet',
        'Workflow Sheet'[Time] >= RELATED('Sign In Sheet'[Start]) && 'Workflow Sheet'[Time] <= RELATED('Sign In Sheet'[Finish])
    )
)
      OTHours = 
CALCULATE(
    COUNT('Workflow Sheet'[Serial]),
    FILTER(
        'Workflow Sheet',
        'Workflow Sheet'[Time] >= RELATED('Sign In Sheet'[O/T Start]) && 'Workflow Sheet'[Time] <= RELATED('Sign In Sheet'[O/T Finish])
    )
)

       

      Best Regards,
      Gao
      Community Support Team

       

      If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
      If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

      How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

      • mouzzampk's avatar
        mouzzampk
        Helper I
        2 years ago

        Hi Gao, 


        Thank you for response.

         

        I have both tables linked to dimnsion table Officer because officer name is different in Sign In and Outgoing Reps and also link to other Calendar Dimension Table for Date.

        I have copied the Formula and changed the Table Name but I am getting the following message. 

         

        This formula is invalid or incomplete: 'The column 'Sign In[Start]' either doesn't exist or doesn't have a relationship to any table available in the current context.'.