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.
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,
Solved! Go to Solution.
@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,
@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
@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,
It works with Tooltip, not in values (I guess the reason being that measure return text values ), I will try Visio visual to achieve this functionality of highlighting floor/room occupancy.
Thanks for the quick feedback.
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 |
---|---|
112 | |
97 | |
84 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |