topic Re: Fitting timestamps into custom time grid in DAX Commands and Tips

Fitting timestamps into custom time grid

YvesBruckmayer — Tue, 01 Nov 2022 08:57:48 GMT

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?

Re: Fitting timestamps into custom time grid

ERD — Tue, 01 Nov 2022 09:52:35 GMT

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

Re: Fitting timestamps into custom time grid

YvesBruckmayer — Tue, 01 Nov 2022 10:19:42 GMT

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.

Re: Fitting timestamps into custom time grid

ERD — Tue, 01 Nov 2022 11:41:47 GMT

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.

Re: Fitting timestamps into custom time grid

YvesBruckmayer — Tue, 01 Nov 2022 15:06:10 GMT

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.

Re: Fitting timestamps into custom time grid

YvesBruckmayer — Tue, 01 Nov 2022 15:12:55 GMT

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!