Forum Discussion
Schedule Matching Strategy using DAX
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.
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.
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.