Count if someone entered but not left

[Anonymous]

Good morning,

I have a database that registers people access in a building, it has more info but the one's I'm looking for are:

Person's ID	Direction	Date an time
1	in	16/10 10:00:00
2	in	16/10 11:00:00
1	out	16/10 11:00:00
3	in	16/10 12:00:00
4	in	16/10 13:00:00

And I want a formula ta count the people that are still IN the building in the current time. So if I looked at this dashboard at 16/10 14:00:00, I want it to show me that there are 3 people inside (ID 2, 3 and 4)
How can I do that?

[amitchandak]

@Anonymous , try a new column

new column =
var _in = countx(filter(Table,able[Persons ID] =earlier([Persons ID]) && [Direction] ="in"),[Persons ID])
var _out = countx(filter(Table,Table[Persons ID] =earlier([Persons ID]) && [Direction] ="in"),[Persons ID])
return
if(_in >_out , "Still There", "Out")

or new measure with person id

new measure =
var _in = countx(filter(allselected(Table),Table[Persons ID] =max([Persons ID]) && [Direction] ="in"),[Persons ID])
var _out = countx(filter(allselected(Table),Table[Persons ID] =max([Persons ID]) && [Direction] ="in"),[Persons ID])
return
if(_in >_out , 1, blank() )

[Anonymous]

@amitchandak  thank you! That's somewhat what I was looking for. But if I want it to be filtered by date and the building the user is on? Is it possible?

[v-deddai1-msft]

Hi @Anonymous ，

You can use the following dax：

Measure =
COUNTAX (
    FILTER (
        SUMMARIZE (
            'Table',
            'Table'[Person's ID],
            "still IN",
                VAR a =
                    CALCULATE (
                        MAX ( 'Table'[Date an time] ),
                        FILTER ( 'Table', 'Table'[Date an time] <= NOW () )
                    )
                VAR b =
                    CALCULATE ( MAX ( 'Table'[Direction] ), 'Table'[Date an time] = a )
                RETURN
                    IF ( b = "in", 1, 0 )
        ),
        [still IN] = 1
    ),
    [Person's ID]
)

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[mahoneypat · Accepted solution]

Please try this expression that should be responsive to your building, etc. slicers.

People In =
VAR thisdatetime =
    MIN ( InOut[Date an time] )
VAR summary =
    ADDCOLUMNS (
        ALLSELECTED ( InOut[Person's ID] ),
        "@ins",
            CALCULATE (
                COUNTROWS ( InOut ),
                InOut[Direction] = "in",
                ALLSELECTED ( InOut[Date an time] ),
                InOut[Date an time] <= thisdatetime
            ),
        "@outs",
            CALCULATE (
                COUNTROWS ( InOut ),
                InOut[Direction] = "out",
                ALLSELECTED ( InOut[Date an time] ),
                InOut[Date an time] <= thisdatetime
            )
    )
RETURN
    COUNTROWS (
        FILTER (
            summary,
            [@ins] > [@outs]
        )
    )

Regards,

Pat

[Anonymous]

Thanks @mahoneypat it worked perfectly!!

If I want to compare the measure People In with the values of another table, how can I do that?

I have the people limit of each building in another table, and I want to show which building are "occupied" or "exceeded" by comparing People In and the max of each building

[mahoneypat]

You can add another variabile to the expression that calculates your max level, also turn the Return with Countrows also into a variable, and then make a new return that divides your People variable by your max level variable.

Regards,

Pat

[Anonymous]

@mahoneypat  I'm sorry, I didn't understand.

Let me explain a little better.

I have this table that I showed you

Person's ID

Direction

Date an time

 that also has a column called Building ID

And then I have another table with the columns:

Building ID

Building Name

Building Max Level

The Measure People In already helped me a lot to show the status of a building in a time.

And I want to put another visual that shows all the building that are Occupied (People In = Building Max Level), Over occupied (People In > Building Max Level), and Free (People In < Building Max Level), but when I want try to make this measure, it says I can't bc Building Max Level is in another table, different from People In

[mahoneypat]

If I understand your model correctly, you should be able to make a table visual with the building column from the 2nd table (had max level column).  You can then aggregate the max level column in a measure (average, min, max shouldn't matter if a single value for max), and then add the People measure to it.

You could also make it a matrix and put Date, Time, etc. as the columns to see how the occupancy changes over time.  Or you can make a measure that give you the % occupied (my previous suggestion). 

% Occupied = var maxlevel = [Max Level Measure]

var people = [People Measure]

return DIVIDE(people, maxlevel)

Regards,

Pat

[Anonymous]

It worked! Thank you!