Fitting timestamps into custom time grid

[YvesBruckmayer]

I need to fit timestamps into a custom time grid.

I have a table with elements where each one defines a booking of a room with a begin time and and end time specifiying the length of the booking.

ID	Room	Begin	End
1111	Room1	1.1.22 8:00:00	1.1.22 8:45:00
1112	Room6	2.1.22 9:00:00	2.1.22 9:45:00
...

This is for a school and the time grid is split into 45min lessons with breaks in between:

Lesson	Begin	End
1	08:00	08:45
2	08:50	09:35
...

I've put this time grid into a separate table. 

My goal now is to get the lesson number for each booking by checking whether the begin time of the booking is larger or equal than the begin time of a given lesson and the end time is smaller or equal. Essentially testing what lesson the booking falls into.

It's fine to assume that no bookings can take up more than one lesson for my purposes.

How would I do this?

[ERD · Accepted solution]

Hello @YvesBruckmayer ,

You can try this measure:

Lesson =
VAR startTime = FORMAT ( SELECTEDVALUE ( Booking[Begin] ), "hh:mm:ss AM/PM" )
VAR endTime = FORMAT ( SELECTEDVALUE ( Booking[End] ), "hh:mm:ss AM/PM" )
VAR lesson =
    CALCULATE (
        MAX ( school[Lesson] ),
        startTime >= FORMAT ( school[Begin], "hh:mm:ss AM/PM" ),
        endTime <= FORMAT ( Booking[End], "hh:mm:ss AM/PM" )
    )
RETURN
    lesson

[YvesBruckmayer]

Hi @ERD 

Thank you. This measure seems to work. At least it looks good when I put into a simple table visual.

However It won't let me use it as a column value in my matrix visual.

Can you not use measures as columns in matrix visuals?

This is my visual as it was with just the hour. 

All I want is to replace the hour columns with the appropriate lesson numbers.

[ERD]

@YvesBruckmayer , I'm not sure I'm following.. You do have a table (a second one) with 'Lesson' column that you can use in your matrix.

[YvesBruckmayer]

@ERD  Nevermind. I think I have what I need.

The same DAX works as a custom column which I can then just select.

Thank you for your help!

[YvesBruckmayer]

@ERD I do but it's currently not related to the booking table at all.

When I try to select the "Lesson" column in the matrix, it just gives me an error.

I also can't create a relation between the two tables because it won't let me create one between a measure and a column.