Forum Discussion

Helen_Brunyee's avatar
Helen_Brunyee
Icon for Helper I rankHelper I
6 years ago
Solved

Occupancy Report

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 scenar...
Need Help
Tips and Tricks
  • AlB's avatar
    AlB
    6 years ago

    Hi Helen_Brunyee 

    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 

     

FrankAT's avatar
FrankAT
Icon for Community Champion rankCommunity Champion
6 years ago

Hi Helen_Brunyee 

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)

Helen_Brunyee's avatar
Helen_Brunyee
Icon for Helper I rankHelper I
6 years ago

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

  • FrankAT's avatar
    FrankAT
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi Helen_Brunyee 

    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)