Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
Ah wauw great solution,
thanks for helping me out!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |