cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted

Custom column advanced editor

Hi Guys,

 

I'm working on a project where I want to show all the reservation for meeting rooms compared with the light sensors of the meeting rooms. So, when someone makes a reservation for a meeting room, do they really use this meeting room during this time.

My reservation table looks like this:

RoomStartdateEnddate
A1.151-1-2018 09:001-1-2018 10:00
A1.161-1-2018 09:001-1-2018 09:30
A1.171-1-2018 09:001-1-2018 09:00
A1.151-1-2018 11:001-1-2018 14:00

 

My calender table looks like this:

Timestamp
1-1-2018 08:15
1-1-2018 08:30
1-1-2018 08:45
1-1-2018 09:00
1-1-2018 09:15
1-1-2018 09:30
1-1-2018 09:45
1-1-2018 10:00
1-1-2018 10:15
1-1-2018 10:30
1-1-2018 10:45
1-1-2018 11:00
1-1-2018 11:15
1-1-2018 11:30
1-1-2018 11:45
1-1-2018 12:00
1-1-2018 12:15
1-1-2018 12:30
1-1-2018 12:45
1-1-2018 13:00
1-1-2018 13:15
1-1-2018 13:30

 

What i want to achieve is a reservation table for each room for every quarter which looks like this:

RoomTimestamp
A1.151-1-2018 09:00
A1.151-1-2018 09:15
A1.151-1-2018 09:30
A1.151-1-2018 09:45
A1.151-1-2018 10:00
A1.161-1-2018 09:00
A1.161-1-2018 09:15
A1.161-1-2018 09:30

Etc.

 

You guys got any idea how to achieve this using the advanced editor?

Thanks in advance

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Custom column advanced editor

Hi @miltenburger,

 

Based on my test, we can use DAX to meet your requirement.

 

Create a calculated table using the formula.

 

Table = CROSSJOIN(calender,reservation)

 

Based on the new table, we can create a calculated column.

 

Column = IF('Table'[Startdate]='Table'[Enddate] || 'Table'[Enddate]>MAX('Table'[Timestamp]) ,BLANK(),IF('Table'[Startdate]<='Table'[Timestamp] && 'Table'[Enddate]>='Table'[Timestamp],'Table'[Room]))

 

Then we can get the result as we need. Please note here we need to filter the blank value out.

 

Capture.PNG

 

For more details, please check the pbix as attached.

 

https://www.dropbox.com/s/lqv1s46i8rzl057/Custom%20column%20advanced%20editor.pbix?dl=0

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Community Support Team
Community Support Team

Re: Custom column advanced editor

Hi @miltenburger,

 

Based on my test, we can use DAX to meet your requirement.

 

Create a calculated table using the formula.

 

Table = CROSSJOIN(calender,reservation)

 

Based on the new table, we can create a calculated column.

 

Column = IF('Table'[Startdate]='Table'[Enddate] || 'Table'[Enddate]>MAX('Table'[Timestamp]) ,BLANK(),IF('Table'[Startdate]<='Table'[Timestamp] && 'Table'[Enddate]>='Table'[Timestamp],'Table'[Room]))

 

Then we can get the result as we need. Please note here we need to filter the blank value out.

 

Capture.PNG

 

For more details, please check the pbix as attached.

 

https://www.dropbox.com/s/lqv1s46i8rzl057/Custom%20column%20advanced%20editor.pbix?dl=0

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Custom column advanced editor

Ah wauw great solution,

thanks for helping me out!