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 

     

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

Hi Helen_Brunyee 

You'll need a date table DateT that includes hours if you want it at that granularity level. Keep it without a relationship to your data table. You can then:

1. Place DateT[Year], DateT[Month], DateT[Date], DateT[DateHour] in a matrix visual

2. Create this measure, format it as percentage and place it in the visual:

BuildingOccupancy =
VAR BuildingCapacity_ = 100 //Enter here the correct value
VAR NumofPeopleInBuilding_ =
    CALCULATE (
        DISTINCTCOUNT ( Table1[Person Name] ),
        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 

 

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

    Hello AlB ,

     

    Thank you so much for this - it is super super helpful!! 🙂

     

    I have followed your steps, however, upon saving the new measure I get the following error:

     

    "A function 'MAX' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

     

    Any ideas on what could be wrong here?

     

    Thanks again,

    Helen

    • AlB's avatar
      AlB
      Icon for Community Champion rankCommunity Champion
      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 

       

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

        AlB this is great - thank you so much for your help 🙂