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.
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 Name | Entry_Date | Exit_Date |
Harry | 12/09/2020 17:40 | 12/09/2020 18:19 |
Oliver | 13/09/2020 10:23 | 14/09/2020 11:14 |
Fred | 12/09/2020 06:38 | 12/09/2020 12:09 |
Maria | 12/09/2020 12:24 | 12/09/2020 12:57 |
Pete | 13/09/2020 09:54 | 14/09/2020 13:47 |
Jacob | 13/09/2020 11:33 | 13/09/2020 17:54 |
Harry | 13/09/2020 12:51 | 13/09/2020 13:53 |
Tony | 13/09/2020 10:59 | 13/09/2020 11:27 |
Oliver | 12/09/2020 10:37 | 12/09/2020 10:39 |
David | 12/09/2020 13:20 | 13/09/2020 20:14 |
Julie | 13/09/2020 13:47 | 13/09/2020 18:44 |
Sarah | 12/09/2020 14:15 | 12/09/2020 14:26 |
Emma | 13/09/2020 10:23 | 13/09/2020 10:25 |
Rachel | 12/09/2020 03:56 | 12/09/2020 05:40 |
Gary | 12/09/2020 07:13 | 12/09/2020 10:03 |
Tom | 12/09/2020 10:43 | 12/09/2020 13:03 |
Fred | 13/09/2020 13:17 | 13/09/2020 16:29 |
Harry | 13/09/2020 12:58 | 13/09/2020 18:14 |
Tony | 13/09/2020 13:08 | 13/09/2020 16:51 |
Mark | 12/09/2020 16:16 | 12/09/2020 17:31 |
Sarah | 13/09/2020 10:36 | 13/09/2020 11:21 |
Maria | 12/09/2020 16:17 | 12/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
Solved! Go to Solution.
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
here is my solution for you:
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
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)
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
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
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
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |