Reply
Member
Posts: 106
Registered: ‎10-10-2017
Accepted Solution

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


Accepted Solutions
Community Support Team
Posts: 2,213
Registered: ‎07-10-2018

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.

View solution in original post


All Replies
Community Support Team
Posts: 2,213
Registered: ‎07-10-2018

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.
Highlighted
Member
Posts: 106
Registered: ‎10-10-2017

Re: Custom column advanced editor

Ah wauw great solution,

thanks for helping me out!