cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helen_Brunyee
Helper I
Helper I

Occupancy Report

Hello,

 

I have been struggling with this for a while now, and cannot seem to get my head around how to go about doing this, so was hoping someone on here might know. I have 'anonymised' the scenario as it is for a work task.

 

I am trying to work out how many people are in a building at any given hour, based on the entry and exit times of visits. My data table is like the below (this is just an example, but hopefully you will get the gist):

 

Person NameEntry_DateExit_Date
Harry12/09/2020 17:4012/09/2020 18:19
Oliver13/09/2020 10:2314/09/2020 11:14
Fred12/09/2020 06:3812/09/2020 12:09
Maria12/09/2020 12:2412/09/2020 12:57
Pete13/09/2020 09:5414/09/2020 13:47
Jacob13/09/2020 11:3313/09/2020 17:54
Harry13/09/2020 12:5113/09/2020 13:53
Tony13/09/2020 10:5913/09/2020 11:27
Oliver12/09/2020 10:3712/09/2020 10:39
David12/09/2020 13:2013/09/2020 20:14
Julie13/09/2020 13:4713/09/2020 18:44
Sarah12/09/2020 14:1512/09/2020 14:26
Emma13/09/2020 10:2313/09/2020 10:25
Rachel12/09/2020 03:5612/09/2020 05:40
Gary12/09/2020 07:1312/09/2020 10:03
Tom12/09/2020 10:4312/09/2020 13:03
Fred13/09/2020 13:1713/09/2020 16:29
Harry13/09/2020 12:5813/09/2020 18:14
Tony13/09/2020 13:0813/09/2020 16:51
Mark12/09/2020 16:1612/09/2020 17:31
Sarah13/09/2020 10:3613/09/2020 11:21
Maria12/09/2020 16:1712/09/2020 18:15

 

I'm looking for a measure (or sequence of measures/steps) that will work out the occupancy of the building each hour (or day), based on whether these people have entered, stayed, exited, or come back again.

 

Hopefully, that makes sense!

 

Any suggestions are really appreciated 🙂

 

Thanks in advance,

Helen

1 ACCEPTED SOLUTION

Hi @Helen_Brunyee 

Right, the simplified version for the filter argument won't be allowed there. Try this instead:

BuildingOccupancy =
VAR BuildingCapacity_ = 100 //Enter here the correct value
VAR NumofPeopleInBuilding_ =
    CALCULATE (
        DISTINCTCOUNT ( Table1[Person Name] ),
        FILTER (
            ALL ( Table1[Entry Date], Table1[Exit Date] ),
            Table1[Entry Date] <= MAX ( DateT[DateHour] )
                && (
                    Table1[Exit Date] > MAX ( DateT[DateHour] )
                        || ISBLANK ( Table1[Exit Date] )
                )
        )
    )
RETURN
    DIVIDE ( NumofPeopleInBuilding_, BuildingCapacity_ )

 

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

 

SU18_powerbi_badge

View solution in original post

8 REPLIES 8
FrankAT
Super User II
Super User II

Hi @Helen_Brunyee 

here is my solution for you:

 

21-09-_2020_12-21-53.png

 

Occupancy of building = 
    CALCULATE(
        CONCATENATEX(VALUES('Table'[Person Name]),'Table'[Person Name],", "), 
        FILTER(
            'Table',
            SELECTEDVALUE('Hour'[Hour]) >= 'Table'[Entry_Time] && 
            SELECTEDVALUE('Hour'[Hour]) <= 'Table'[Exit_Time] && 
            SELECTEDVALUE('Calendar'[Date]) = 'Table'[Entry_Date] 
        )
    )

 

You'll find attached pbix file.

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

Hi @FrankAT ,

 

This is brilliant thank you! I have managed to get this working 🙂 My only question is how would I change the DAX so that it is a count of people (distinct count) in the building instead of a list of names? In some cases, I have a lot of people so a number would be preferable to a list.

 

Thank you again,

 

Helen

Hi @Helen_Brunyee 

I revised the former pbix file , so here is the new one (attached below), with selectable hour, minute and date. Never the less I added a chart.

 

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

 

@FrankAT You're a star - thank you!

AlB
Super User III
Super User III

Hi @Helen_Brunyee 

You'll need a date table DateT that includes hours if you want it at that granularity level. Keep it without a relationship to your data table. You can then:

1. Place DateT[Year], DateT[Month], DateT[Date], DateT[DateHour] in a matrix visual

2. Create this measure, format it as percentage and place it in the visual:

BuildingOccupancy =
VAR BuildingCapacity_ = 100 //Enter here the correct value
VAR NumofPeopleInBuilding_ =
    CALCULATE (
        DISTINCTCOUNT ( Table1[Person Name] ),
        Table1[Entry Date] <= MAX ( DateT[DateHour] )
            && (
                Table1[Exit Date] > MAX ( DateT[DateHour] )
                    || ISBLANK ( Table1[Exit Date] )
            )
    )
RETURN
    DIVIDE ( NumofPeopleInBuilding_, BuildingCapacity_ )

 

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

 

SU18_powerbi_badge

Hello @AlB ,

 

Thank you so much for this - it is super super helpful!! 🙂

 

I have followed your steps, however, upon saving the new measure I get the following error:

 

"A function 'MAX' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

 

Any ideas on what could be wrong here?

 

Thanks again,

Helen

Hi @Helen_Brunyee 

Right, the simplified version for the filter argument won't be allowed there. Try this instead:

BuildingOccupancy =
VAR BuildingCapacity_ = 100 //Enter here the correct value
VAR NumofPeopleInBuilding_ =
    CALCULATE (
        DISTINCTCOUNT ( Table1[Person Name] ),
        FILTER (
            ALL ( Table1[Entry Date], Table1[Exit Date] ),
            Table1[Entry Date] <= MAX ( DateT[DateHour] )
                && (
                    Table1[Exit Date] > MAX ( DateT[DateHour] )
                        || ISBLANK ( Table1[Exit Date] )
                )
        )
    )
RETURN
    DIVIDE ( NumofPeopleInBuilding_, BuildingCapacity_ )

 

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

 

SU18_powerbi_badge

View solution in original post

@AlB this is great - thank you so much for your help 🙂

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors