Forum Discussion
Matching two different range times from two different tables
Hi experts J
I’m struggling trying to do something… a bit tricky.
I got a range of TIME with availability (let say we have football field which can be booked by X minutes, let say 60 to simplify a bit)
So I have a table which contains availability, hour by hour :
08:00:00 09:00:00
09:00:00 10:00:00
.
.
.
.
.
22 :00 :00 23:00:00
So in total we have in total 15 slots that can be booked.
Those slots can change considering if Monday or Saturday for example (here let say it’s all the same).
So in a week, we have 105 slots.
This is the content of a table, call it [TABLE A]
In the other table, [TABLE B], I got the reservations. And here is the tricky thing.
The starting time and ending time can be on both slots (example : from 08:30:00 to 09:30:00)
The availability is the same as if the field was booked from 08 to 10 in fact : 2.
So I’d like to display ‘1’ in front of the first slot, and ‘1’ in front of the second.
And here is the jigsaw.
I managed to display this :
Slot Start Slot End Availability
08:00:00 09:00:00 1
09:00:00 10:00:00 1
.
.
.
.
.
22 :00 :00 23:00:00 1
TOTAL 15
If I play with the date slicer, the availability is increasing (for 10 days I got 10 availabilities) so it’s all good.
But what I’m trying to do is
Slot Start Slot End Availability Reservations Rate
08:00:00 09:00:00 1 1 100%
09:00:00 10:00:00 1 1 100%
.
.
.
.
.
22 :00 :00 23:00:00 1 0 0%
TOTAL 15 2 13.33%
I just don’t know how to something like that, the only thing I managed to have is :
Slot Start Slot End Availability Reservations Rate
08:00:00 09:00:00 1 2 100%
09:00:00 10:00:00 1 2 100%
.
.
.
.
.
22 :00 :00 23:00:00 1 2 0%
TOTAL 15 2 13.33%
Showing the total at every single slot, but I did not manage to get the down-drilled information. I tried with LOOKUPVALUE, I tried to play with FILTER(), with ALL(), but I never manage to do that.
The join between [TABLE A] and [TABLE B] is a “Many to Many” with [TABLE B] filtering [TABLE A] (the thing is we want to display these info by day of week).
The date slicer is on Reservation Date coming from [TABLE B], but I can filter via another table if needed (for context issues, for example).
Well here is my trouble, I don’t think it’s an easy thing, if anyone has an idea, I’m all ears J
Have a good day.
Heykel
Anonymous You may consider removing the relationship between the tables and then looking for reservations with a measure.
Reservations = SUMX(AvailableTable, CALCULATE(COALESCE ( CALCULATE ( COUNTROWS ( ReservationsTable ), FILTER ( ReservationsTable, ReservationsTable[Date] = max(AvailableTable[Date]) && (TIME ( ROUNDDOWN ( VALUE ( ReservationsTable[StartTime] ) * 24, 0 ), 0, 0 ) = MIN ( AvailableTable[StartTime] ) || TIME ( ROUNDUP ( VALUE ( ReservationsTable[EndTime] ) * 24, 0 ), 0, 0 ) = MIN ( AvailableTable[EndTime] )) ) ), 0 )))You would then use the AvailableTable for the date and time on any visualization or table. The idea is to simply check for a reservation on that day, where the start or end time is on that slot (I rounded the start times down, and the end times up) to account for reservation that took two slots by being on the half hour. This does assume you have a seperate "Field" table that is joined to both AvailableTable and ReservationsTable, otherwise you would need to add in ReservationsTable[Field] = min(AvailableTable[Field] as well.
I think it is ok to have the AvailableTable to have each day/time/field in it, I don't foresee that getting into the millions of rows territory.
Edit: Modified the measure to also calculate the sum correctly. And put () around the StartTime or EndTime condition in the filter 🙂
6 Replies
- amitchandak
Super User
Anonymous , You need some common level to take diff.
- Thank you amitchandak I can see that yep, it's a database law for sure.
- DataZoe
Microsoft Employee
Anonymous Does the reservations table have a column that will specify a single reservation id? So, say Sam is the person reserving from 8:30a - 9:30a (which is 2 slots, 8a-10a), or it's reservation ID 123. You could then change the Reservations measure to :
Reservations = distinctcount(TableB[ReservationID])
I am not sure how you are determining the different days in your data model, can you explain that?
DataZoe yes it does. And Yes, I already do that. The only thing is this, give me the overall result but does not split by hours ranges.
In the reservation table I do have a date, in my schedule table I have day of week for each fields.
The only solution I can see here is to duplicate the date information in my schedule table, i.e :
Field A Monday 10-08-2020 08:00:00 23:00:00
Field A Monday 03-08-2020 08:00:00 23:00:00
And so on, for every single field and for every day.
I guess this could make my table becomes huge, that's not what I'm looking for...
- DataZoe
Anonymous You may consider removing the relationship between the tables and then looking for reservations with a measure.
Reservations = SUMX(AvailableTable, CALCULATE(COALESCE ( CALCULATE ( COUNTROWS ( ReservationsTable ), FILTER ( ReservationsTable, ReservationsTable[Date] = max(AvailableTable[Date]) && (TIME ( ROUNDDOWN ( VALUE ( ReservationsTable[StartTime] ) * 24, 0 ), 0, 0 ) = MIN ( AvailableTable[StartTime] ) || TIME ( ROUNDUP ( VALUE ( ReservationsTable[EndTime] ) * 24, 0 ), 0, 0 ) = MIN ( AvailableTable[EndTime] )) ) ), 0 )))You would then use the AvailableTable for the date and time on any visualization or table. The idea is to simply check for a reservation on that day, where the start or end time is on that slot (I rounded the start times down, and the end times up) to account for reservation that took two slots by being on the half hour. This does assume you have a seperate "Field" table that is joined to both AvailableTable and ReservationsTable, otherwise you would need to add in ReservationsTable[Field] = min(AvailableTable[Field] as well.
I think it is ok to have the AvailableTable to have each day/time/field in it, I don't foresee that getting into the millions of rows territory.
Edit: Modified the measure to also calculate the sum correctly. And put () around the StartTime or EndTime condition in the filter 🙂
