How to identify employees that overlap with dates and times of another selected (slicer) employee?

[Anonymous]

I'm wondering what the best way of approaching this problem is. The requirement is that we can select any employee (slicer) and a date range (from / to) and return (as well as count) all other employees that are present within the same date and time window. 

E.g. If we look at this sample data: 

	Name		From Date and Time		To Date and Time
	Billy Bloggs		15/01/2021 08:00:00		15/01/2021 12:00:00
	Mary Smith		15/01/2021 08:30:00		15/01/2021 17:00:00
	Fat Cat		15/01/2021 09:00:00		15/01/2021 16:00:00
	Somebody Else		15/01/2021 13:00:00		15/01/2021 20:00:00
	Billy Bloggs		16/01/2021 08:00:00		16/01/2021 10:00:00
	Fat Cat		16/01/2021 09:00:00		16/01/2021 09:30:00
	Humphry Bear		16/01/2021 12:00:00		16/01/2021 14:30:00
	...

If Billy Bloggs was selected in the slicer, we would identify: 

• Mary Smith (15/01/2021)
• Fat Cat (15/01/2021)
• Fat Cat (16/01/2021)

As being records that overlap with the dates and times associated with BIlly Bloggs (Somebody Else and Humphry Bear are present onthe same days but do not overlap with the times - start after Billy has finished on both days). 

Data can be referenced / duplciated to make dimension tables and making a master date table is fine too. Currenmtly I just have a flat table exported from a security / card access system. 

I'd appreciate any tips that can get me going in the right direction. 

[Anonymous]

You're a good man @Jihwan_Kim thank you so much for your help! 

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

I am not sure if I understood your question correctly, but please check the below picture and the sample pbix file's link down below.

Identify measure =
VAR selectfromslicer =
SELECTEDVALUE ( Names[Name] )
VAR conditiontable =
FILTER ( ALL ( Data ), Data[Name] = selectfromslicer )
VAR eachrowcondition =
FILTER (
conditiontable,
MAX ( Data[From Date and Time] ) <= Data[To Date and Time]
&& MAX ( Data[To Date and Time] ) >= Data[From Date and Time]
)
RETURN
IF (
SELECTEDVALUE ( Data[Name] ) = selectfromslicer,
BLANK (),
IF ( COUNTROWS ( eachrowcondition ) > 0, 1, BLANK () )
)

https://www.dropbox.com/s/5wc904s0frhppox/ajclark.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM