Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
kerre
New Member

Monitoring Flex desk availability based on dynamic start/endDate

Hi ,

 

I'm at very beginning of designing a "flex desk reservation" application by means of Power BI, Power Automate and Power App. Before getting to automating leg of the project, I'd like to solve some problems with dummy data.

 

My problem is to monitor available flex desks within the date range one would like to make a new reservation. Some practical information:  There are 5 Tables  and model is as follows.

kerre_0-1628689407183.png

1) The Start/End Date tables are used for choosing the date range one reserves a flex table: it can be only one day (Start Date = end Date) or for multiple subsequent days ( Start Date < End Date ) 

2) Desks Table has attributes like DeskNo, Floor, Flex(Yess,No) etc.

3) Users Table has attributes such as First Name, Last Name, Full Name, Emp.ID etc.

4)Reservation Table (basically the fact table) has the log of reservations: DeskNo, Reserved by(User Full Name), Start Date, End Date

 

I have 2 measures that holds the Requested reservation Dates:

Selected Value Start Date = 'Start Date'[Date]
Selected Value Start Date = 'End Date'[Date]
 
My Reservations Table (Fact Table):
 
Reserved by --  Desk -- Start Date   ---  EndDate
CR                    107        01/09/2021       02/09/2021
DM                   122        03/09/2021       03/09/2021
DM                   153        08/09/2021       10/09/2021
KDS                  200        06/09/2021       07/09/2021
KDS                  202        12/09/2021       12/09/2021
KY                    107         06/09/2021       07/09/2021
KY                    122         13/09/2021       14/09/2021
NH                   105         01/09/2021       01/09/2021
NH                   107         03/09/2021       06/09/2021
NH                   107         14/09/2021       14/09/2021
 
So I wrote a measure to use in filtering available and occupied desks:
(F)Filter Occupied Desks =
                          IF (MAX ( Reservations[StartDate] ) <= [Selected Value End Date]
                                           && MAX ( Reservations[EndDate] ) >= [Selected Value Start Date],1,0)
 
When I select as follows:
Start Date: 03/09/2021
End Date : 03/09/2021 
Basically there are 2 occupied desks, 122 and 107, however they are also flagged "available" according to other 2 records, so when I use my measure for filtering  Occupied Desks table visualisation (includes all desks in the building) I get no ocuppied desks, although desk 107 and 122 are occupied withing the choosen dates:
 
Reserved by --  Desk -- Start Date   ---  EndDate
CR                    107        01/09/2021       02/09/2021  flags "available"
DM                   122        03/09/2021       03/09/2021 flags "occupied" 
DM                   153        08/09/2021       10/09/2021
KDS                  200        06/09/2021       07/09/2021
KDS                  202        12/09/2021       12/09/2021
KY                    107         06/09/2021       07/09/2021
KY                    122         13/09/2021       14/09/2021 flags "available"
NH                   105         01/09/2021       01/09/2021
NH                   107         03/09/2021       06/09/2021 flags "occupied"
NH                   107         14/09/2021       14/09/2021
   kerre_1-1628691668130.png

While I choose some other dates:

Start Date : 08/09/2021

End Date: 09/09/2021

There is only one record within this dates and this desk has also only one record in my fact table so I can see desk 153 in the filtered Occuppied Desks table visualisation:

 

Reserved by --  Desk -- Start Date   ---  EndDate
CR                    107        01/09/2021       02/09/2021
DM                   122        03/09/2021       03/09/2021
DM                   153        08/09/2021       10/09/2021  flagged "occupied"
KDS                  200        06/09/2021       07/09/2021
KDS                  202        12/09/2021       12/09/2021
KY                    107         06/09/2021       07/09/2021
KY                    122         13/09/2021       14/09/2021
NH                   105         01/09/2021       01/09/2021
NH                   107         03/09/2021       06/09/2021
NH                   107         14/09/2021       14/09/2021
kerre_2-1628691959980.png

I tried to change my filter measure so that if a desk is flagged as 1 ("occupied") in one record and flagged as 0 ("available") on an other, it'd be flagged always as 1 "occupied". My new measure bacame like:

(F)Filter Occupied Desks = MAXX(SUMMARIZE(Reservations, Reservations[Desk], "Occupation",
                                                                            IF (MAX ( Reservations[StartDate] ) <= [Selected Value End Date]
                                                                            && MAX ( Reservations[EndDate] ) >= [Selected Value Start Date],1,0)),                                                                           [Occupation])
 
I also thought to create a table with SELECTCOLUMNS() where I first flag each record by the If statement above and group and sum up occupation flags by SUMMARIZE() function so that if a desk is once flagged occupied its sum of occupation level would be always above 0 but this hasn't given either any desirable result as filter on Occupied Desks table visuaization.
 
Community's help is much appriciated.
 
Thanks in advance 
 
Kerre

 

2 REPLIES 2
amitchandak
Super User
Super User

@kerre , Try to use an indpendent dat table as slicer and then try meausre like

 

measure =
VAR _max = MAXX(allselected("Date"),"Date" [Date])
var _min = MinX(allselected("Date"),"Date" [Date])
return
IF (Min ( Reservations[StartDate] ) <= _min
&& MAX ( Reservations[EndDate] ) >= _max,1,0)

Thanks @amitchandak  for your reply.

I actually have 2 independent Date tables named Start Date and End Date of which selected values are hold in 2 measures:

Selected Value Start Date = 'Start Date'[Date]
Selected Value Start Date = 'End Date'[Date]

kerre_0-1628689407183.png

I have implemented your formula as follows but that returns now no 1's ("ocuppied") at all. e.g. I choose whole month September in which there should deffinitely be 6 occupied desks.

(F)Filter Occupied Desks =
                    var _max = MAXX(allselected('Start Date'),'Start Date')
                    var _min = MINX(allselected('End Date'), 'End Date'[Date])
                   return
IF (MIN(Reservations[StartDate]) <= _min && MAX(Reservations[EndDate]) >= _max, 1 ,0)
 
kerre_0-1628759253753.png

 

Did I implemented your formula wrong then?

 

Thanks in advance,

Kerre

 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors