Get max concurrent call count by the hour

pstew12 ,

 

Firstly generate a 24h series table using dax:

Hours = GENERATESERIES(0, 23, 1)

Then you can create a table like below, you can add other conditions in the switch expression.

Result Table = 
SELECTCOLUMNS (
    Hours,
    "Hour of day(24hr)", Hours[Value],
    "Max Calls", SWITCH (
        Hours[Value],
        8, CALCULATE (
            COUNTROWS ( 'Table' ),
            FILTER (
                'Table',
                NOT (
                    HOUR ( 'Table'[JoinTime] ) >= 9
                        || HOUR ( 'Table'[LeaveTime] ) < 8
                )
            )
        ),
        9, CALCULATE (
            COUNTROWS ( 'Table' ),
            FILTER (
                'Table',
                NOT (
                    HOUR ( 'Table'[JoinTime] ) >= 10
                        || HOUR ( 'Table'[LeaveTime] ) < 9
                )
            )
        )
    )
)

 

Please also refer to the pbix file attached.

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.