Forum Discussion
Power BI: Find out occupancy using Date Filter(Slicer)
I have a scenario where I want to find the occupancy by selecting a date in a slicer
Details are as below
Room Table: Room Id and Room name
| Room ID | Room Name |
| 1 | R101 |
| 2 | R102 |
| 3 | R103 |
| 4 | R104 |
| 5 | R105 |
Occupancy Details Table: Contains the historical data for each room and for the period for which it was occupied.
| Room ID | Coustomer ID | Room Name | Start Date | End Date |
| 1 | 1 | R101 | Jan-17 | Mar-17 |
| 1 | 2 | R101 | Apr-17 | Jun-17 |
| 1 | 3 | R101 | Jul-17 | Sep-17 |
| 2 | 4 | R102 | Mar-17 | Jun-17 |
| 3 | 5 | R103 | Jan-17 | Sep-17 |
| 4 | 6 | R104 | Apr-17 | Dec-17 |
| 5 | 7 | R105 | Jan-17 | Jun-17 |
Now i want to pass a data and find out which rooms were occupied and vacant on a particular date.
Eg If I pass date as Jul-2017
I will get R101, R103 and R104 as Occupied rooms.
However, While calculating the Vacant Rooms historical records of R101 are also considered.
I want a workaround by which I could exclude the historical records of all the currently occupied Rooms.
Desired output
Date Filter - Jul 2017
Occupied Rooms: R101, R103, and R104
Vacant Rooms: R102 and R105
Please let me know if you need further details and looking forward to your feedback.
Thanks,
Anonymous
In this scenario, you need to create a full calendar table. And don't create relationship between Calendar table and this Book table.
Then create a flag measure to determine if the room is available.
IsAvailable = IF( MAX('Calendar'[Date]) >=MAX(Book[Start Date ]) && MAX('Calendar'[Date]) <=MAX(Book[End Date] ),0,1)Then add this measure in to Visual Level Filter.
Regards,
Anonymous
The Tree Map should only accept hierarchical data in Group or Detail, which means the values should be "fixed". You can't put a measure. You may try to add measure into Value or Tooltip.
Regards,
4 Replies
v-sihou-msftMicrosoft Employee
Anonymous
In this scenario, you need to create a full calendar table. And don't create relationship between Calendar table and this Book table.
Then create a flag measure to determine if the room is available.
IsAvailable = IF( MAX('Calendar'[Date]) >=MAX(Book[Start Date ]) && MAX('Calendar'[Date]) <=MAX(Book[End Date] ),0,1)Then add this measure in to Visual Level Filter.
Regards,
Thanks, Using the flag to identify the occupancy of the room had resolved my problem.
I have a one follow-up question.I have created a calculated measure as you have suggested
IsAvailable = IF( MAX('Calendar'[Date]) >=MAX(Book[Start Date ]) && MAX('Calendar'[Date]) <=MAX(Book[End Date] ),0,1)Then I have used this calculated measure as below
Occupant = CALCULATE( If( [IsAvailable]=1 , MAX([Tenant Name]) , MAX([Room Name])))
This Measure displays "Tenant Name" if it is occupied else it will display "Room Name".
This measure works correctly in matrix visuals. However, I am not allowed to use this measure (Occupant)in Treemap as group or
detail values.
Is this a limitation of Treemap or am I missing something?
Thanks- v-sihou-msft
Anonymous
The Tree Map should only accept hierarchical data in Group or Detail, which means the values should be "fixed". You can't put a measure. You may try to add measure into Value or Tooltip.
Regards,