Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
YvesBruckmayer
New Member

Fitting timestamps into custom time grid

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.

IDRoomBeginEnd
1111Room11.1.22 8:00:001.1.22 8:45:00
1112Room62.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:

LessonBeginEnd
108:0008:45
208:5009: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?

1 ACCEPTED SOLUTION
ERD
Super User
Super User

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!

View solution in original post

5 REPLIES 5
ERD
Super User
Super User

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. 

YvesBruckmayer_0-1667297914543.png

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.

 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors