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

[mouzzampk]

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 far 

=COUNT('Outgoing Reps'[PCN])

Basically I am trying to calculate the number of letters issued between normal hours and overtime hours.

Please let me know if you need more information. 

Thank you

[Anonymous]

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]

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]

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]

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.'.

[mouzzampk]

Is there anyway I can fix this? I am just playing around but not finding a correct solution 🙂 

I don't see Sign In table in the dropdown but I have this table linked to dimension table.

Thank you

[Anonymous]

Hi @mouzzampk ,

Are you using excel? Please try to refer to the steps in this document to create a relationship.

Create a relationship between tables in Excel

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]

Hi Gao

Thank you for helping me out with this. I have attached the spreadsheet. As you can see I have Officer fields from both Sign In and Outgoing Reps linked to Dimesion Table (Officer) 

I hope the attached spreadsheet will give more details.

https://docs.google.com/spreadsheets/d/1OiJakJOhKzTCGo26CeP3u-l7zpSZvWx0/edit?usp=sharing&ouid=10459... 

Thank you

mouzzampk