cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gipiluso
Advisor

Occupancy: calculate the max number of people present in an area at the same time

Hi All,

I have an interesting problem to solve in Power BI.

I would like to monitor the “max number of people” present in an area at the same time.

 

occupancy example.PNG

I tried a couple of way but are not in line with the requirements:

  • Calculate the number of entrance in the area in the timeframe – Does not consider if the people are not present at the same time in the area
  • Calculate at backend, as per the example – It is not dynamic on filter.

 

Basically, I wasn’t able to obtain in dax the column “count”.

How did you managed similar requirements? Do you have any suggestion?

 

Thanks in advance for your support.

BR

GP

1 ACCEPTED SOLUTION

Accepted Solutions
gipiluso
Advisor

Re: Occupancy: calculate the max number of people present in an area at the same time

Hi v-lili6-msft ,

thanks for your opinion.

Unfortunately, your measure has a problem, filtering a single resource in the slicer you got the max values of this, but a resource can take just a slot (see below).

 

I resolved with 

Occupancy in Room = CALCULATE (
SUM (FactOccupancy[InOut]),FILTER(ALLSELECTED(FactOccupancy), FactOccupancy[DateTime] <= MAX ( FactOccupancy[DateTime] )
),
FILTERs(
FactOccupancy[area])

)

 

Max Occupancy = MAXX (
VALUES(FactOccupancy),FactOccupancy[Occupancy in Room])

 

problem_measures.PNG

 

View solution in original post

4 REPLIES 4
Community Support Team
Community Support Team

Re: Occupancy: calculate the max number of people present in an area at the same time

hi, @gipiluso

Take a look at these two Quick Measures as I think you want something like them.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

 

But in your case, You need to think deeper, It also needs a daily timetable, I recommend you only consider time table grows in ten minutes and compare by timenumber like below

time number = HOUR(TimeTable[time])*100+MINUTE(TimeTable[time])

12.JPG

otherwise the calculation will gett bigger and bigger.

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
gipiluso
Advisor

Re: Occupancy: calculate the max number of people present in an area at the same time

actually the problem is not calculate the column count.

Also with the support of Reza (the great one) I was able to calculate the measure on dax with cumulative total approach:

Occupancy in Room = CALCULATE (
SUM (Occupancy[InOut]),
FILTER (
ALLEXCEPT( Occupancy,Occupancy[area]),
Occupancy[DateTime] <= MAX ( Occupancy[DateTime] )
)
)

the problem is that my requirement is calculate the max per area dynamic with slicer

 

 occupancy example cumulative.PNG

Highlighted
Community Support Team
Community Support Team

Re: Occupancy: calculate the max number of people present in an area at the same time

hi, @gipiluso

After my test in your way, there is no slicer for datetime for now,

And you may try this improved measure based on your formula:

Measure = var _table=SUMMARIZE('Table','Table'[event time],'Table'[area],"OccupancyRoom",CALCULATE (
SUM ('Table'[InOut]),
FILTER (
ALLEXCEPT( 'Table','Table'[area]),
 'Table'[event time]<= MAX ('Table'[event time])
)
)) return
CALCULATE(MAXX(_table,[OccupancyRoom]))

Result:

5.JPG

Here is pbix, please try it.

https://www.dropbox.com/s/iridr33knsqdjfu/calculate%20the%20max%20number%20of%20people%20present%20i...

 

Best Regards,

Lin

 

 

 

 

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
gipiluso
Advisor

Re: Occupancy: calculate the max number of people present in an area at the same time

Hi v-lili6-msft ,

thanks for your opinion.

Unfortunately, your measure has a problem, filtering a single resource in the slicer you got the max values of this, but a resource can take just a slot (see below).

 

I resolved with 

Occupancy in Room = CALCULATE (
SUM (FactOccupancy[InOut]),FILTER(ALLSELECTED(FactOccupancy), FactOccupancy[DateTime] <= MAX ( FactOccupancy[DateTime] )
),
FILTERs(
FactOccupancy[area])

)

 

Max Occupancy = MAXX (
VALUES(FactOccupancy),FactOccupancy[Occupancy in Room])

 

problem_measures.PNG

 

View solution in original post

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 471 members 4,110 guests
Please welcome our newest community members: