Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Solved! Go to 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
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
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
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
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.
@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.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
@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!
@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.