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.
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:
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
Solved! Go to 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])
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
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
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.
Best Regards,
Lin
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])
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 |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |