Custom column advanced editor

[miltenburger]

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:

Room	Startdate	Enddate
A1.15	1-1-2018 09:00	1-1-2018 10:00
A1.16	1-1-2018 09:00	1-1-2018 09:30
A1.17	1-1-2018 09:00	1-1-2018 09:00
A1.15	1-1-2018 11:00	1-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:

Room	Timestamp
A1.15	1-1-2018 09:00
A1.15	1-1-2018 09:15
A1.15	1-1-2018 09:30
A1.15	1-1-2018 09:45
A1.15	1-1-2018 10:00
A1.16	1-1-2018 09:00
A1.16	1-1-2018 09:15
A1.16	1-1-2018 09:30

Etc.

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

Thanks in advance

[v-frfei-msft · Accepted solution]

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.

For more details, please check the pbix as attached.

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

Regards,

Frank

[miltenburger]

Ah wauw great solution,

thanks for helping me out!