Calculate how often a threshold between two timestamps has been reached.

[ChromeMystic]

Hi everyone

We have a problem with some of our Agents starting their shifts to late or ending them to early, and since we cannot (and don't want to, as most of them are able to read their shiftplans no problem) monitor them live all the time. I am trying to write a report, that marks those who had many discrepancies between  their planned shift, and their actual working times, so we can check these directly instead off manually tracking all logins and logouts for all agents manually.

I got the a table with all Logins and Logouts of our agents into our telephone system (tableLogin), a lookup table with the official start and end times for our shifts (tableShifts) as well as a table with the planned shifts per agent (tablePlanning).

I have managed to write the lookups to get the following data:

I blurred out the names for privacy reasons, and some shifts (such as 320) don't have the right calculations yet, as they start in the evening of one day and end on the next. 
"loginDiff" is a measure that calculates the difference between the planned start of the shift and the first login of said agent. This works fine and in "loginWarning" I have the same measure but only if said discrepancy is 6 minutes or more.

loginDiff = 
IF (
    TIMEVALUE ( [shiftTimeStart] ) <= [firstLogin],
    DATEDIFF ( TIMEVALUE ( [shiftTimeStart] ), [firstLogin], MINUTE ),
    - DATEDIFF ( [firstLogin], TIMEVALUE ( [shiftTimeStart] ), MINUTE )
)

What I am trying to do now, is to create a measure that lists all agents with more than a certain number of discrepancies over 6 minutes in a certain timeframe. I.e. Agent 1 started work 8 minutes late, 10 minutes late and 12 minutes late in the last 25 workdays. I would like to have a list with his name and the number 3 for the total counts of delayed work start. From there I will link to a detailed report about this agents time management.
My first idea was to use a filtered "count" function but those only work on tables and not within a measure. I would rather not merge the tableLogin, tableShifts and tablePlanning into one table, as I will have to use the different tables combined with other data in the future and would dublicate this data in my model.

Do you have any tips for me how I might be able to accomplish this?

[johnt75 · Accepted solution]

I think something like the below should work

Num discrepancies =
VAR summaryTable =
    CALCULATETABLE (
        ADDCOLUMNS ( VALUES ( 'tableShifts'[Shift ID] ), "@loginDiff", [loginDiff] ),
        'tableShifts'[Start date]
            >= TODAY () - 25
    )
RETURN
    COUNTROWS ( FILTER ( summaryTable, [@loginDiff] >= 6 ) )

[ChromeMystic]

Thank you! This gets me where I want to go - now I can start debugging my Data.