Forum Discussion

miltenburger's avatar
miltenburger
Helper V
8 years ago
Solved

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

Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    8 years ago

    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

2 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    8 years ago

    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