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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
gipiluso
Resolver II
Resolver II

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

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
v-lili6-msft
Community Support
Community Support

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.

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

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.

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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