Schedule Matching Strategy using DAX

[jake_BIVisual]

I have an interesting business problem that I would like to use DAX to solve and was wondering if anyone has attacked something similar. I am at an intermediate level for DAX, and was looking more for a general strategy than specific DAX code (though I can use either to move forward). I am limiting this question to DAX (not M or power query) as I want the measure to interact with filter context in the UI.

Background:  There are two entities (Clients and Providers) who have their base availability (think working day), then either can have Existing appts which "block" out some portion of their availability.  I'd like to "flag" any openings (Schedule Matches) where there is 1.5 hr or greater opening on both schedules.
Example: 
Client 1: Is available from 8am-5pm and has (2) appts: 9am-11am and 3pm-5pm
Provider1: Is availble from 7am-6pm and has (1) appt: 11am-1pm

The only "Scheduling Match" will be between 1pm-3pm. 

Any strategies or ideas would be appreciated. I will post a solution when I find one.

[Anonymous]

Well, let's say that the availability hours and appointments can't span more than 1 day and a day is the unit we're looking in for the 1.5+hr openings. I just don't get what you mean by "flag" any openings. Since you want a measure I can, for instance, create one that will for any combination (it can be generalized to any set of combinations) of clients and providers return a boolean saying True if there's at leas one such opening and False otherwise. Here's how to do it at a very granular level. Assume you're looking at just one day in your calendar and teh granularity of time is, say, 1 minute. Just have a Time dimension that covers 24 hours in 1-min chunks. Then create a factless fact table that will pair clients/providers with days and times in such a way that a row with ClientID/ProviderID, Day, Time, Status will tell you whether the Client/Provider on this Day was available (Status="A") or not (Status="N/A") in this minute. Once you've got a table like this, it's not hard to calculate the boolean flag defined as above.

[jake_BIVisual · Accepted solution]

@Anonymous  I appreciate it.

The solution:

I took a variation of DAXERS approach with by creating a TimeTable down to the minute:
[Index] [Time XX:XX:00]

I then created Table Variables in this pattern:

tblAvailabilityClient: Filtered TimeTime for Clients Available Times
tblAvailabilityProvider: Filtered TimeTable for Providers Available Times

tblScheduleClient: Filtered TimeTable for Clients existing Schedule
tblScheduleProvider: Filtered TimeTable for Clients existing Schedule

tblClientOpen= Except (tblAvailabilityClient,tblScheduleClient): Filtered only the remaining availability after accounting for what is already scheduled.
tblProviderOpen=Except (tblAvailabilityProvider,tblScheduleProvider): Filtered only the remaining availability after accounting for what is already scheduled.

tblMatches= Intersect(tblClientOpen,tblProviderOpen): Filtered for only those times that are open for both the client and provider.


That is the base pattern, then I iterated over the effective dates.