cancel
Showing results for
Did you mean:

## 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.

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?

BR

GP

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

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

Hi v-lili6-msft ,

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])

4 REPLIES 4
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])`

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.

## 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

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:

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.
Highlighted

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

Hi v-lili6-msft ,

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])

Announcements