Report Help to Find Insiders

[koray]

Hello,

I am trying to make a report that shows whether certain people are currently inside or outside our factory. People read cards at turnstiles and these records are entered into a program's database

The relevant table I got with direct query is as follows.

Name	Surname	Door	Last Event Date
Alex	X	Entrance Turnstile	29.08.2023 11:48
Ally	X	Exit Turnstile	29.08.2023 10:18
John	X	Exit Turnstile	29.08.2023 07:00
Tanya	X	Exit Turnstile	29.08.2023 16:00
Alex	X	Exit Turnstile	29.08.2023 15:30
Ally	X	Entrance Turnstile	29.08.2023 11:54
John	X	Entrance Turnstile	29.08.2023 15:00

Each time a person swipes their card at the turnstile, a record is made in this table. I want to show the people who are inside with the "Entrance Turnstile" card according to the latest event date in the table. So when I look at the report, I want to see who is in there right now. I want the result of the table above to be as follows.

Name	Surname	Door	Last Event Date	Status
John	X	Entrance Turnstile	29.08.2023 15:00	Inside
Ally	X	Entrance Turnstile	29.08.2023 11:54	Inside

According to the first table, John and Ally are inside, so I only want to see them in my table. How can I do that?

Thanks for help

Regards.

[v-cgao-msft]

Hi @koray ,

Please create a new measure:

Status = 
VAR __name = MAX('YourTableName'[Name])
VAR __last_event_date = CALCULATE(MAX('YourTableName'[Last Event Date]),FILTER(ALLSELECTED('YourTableName'),'YourTableName'[Name]=__name))
VAR _status = IF(MAX('YourTableName'[Last Event Date])=__last_event_date && MAX('YourTableName'[Door])="Entrance Turnstile","Inside")
RETURN
_status

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[koray]

Hello @v-cgao-msft 

Thank you very much. I think it's working. I am very happy 🙂
I forgot one point. Can I add one more to the door names?

MAX('VAccessControlLog'[DoorName])="STAFF TURNER 01 ENTRANCE", "Inside")

I would like to add the PERSONNEL TURNER 02 ENTRANCE door in the door name section above. How should I make an arrangement?

Status =

VAR __name = MAX(VAccessControlLog[Name])

VAR __last_event_date = CALCULATE(MAX(VAccessControlLog[EventDate]),FILTER(ALLSELECTED('VAccessControlLog'),'VAccessControlLog'[Name]=__name))

VAR _status = IF(MAX('VAccessControlLog'[EventDate])=__last_event_date && MAX('VAccessControlLog'[DoorName])="PERSONEL TURNİKE 01 GİRİŞ","Inside")

RETURN

_status

Regards

[v-cgao-msft · Accepted solution]

Hi @koray ,

Please try:

Status =
VAR __name =
    MAX ( VAccessControlLog[Name] )
VAR __last_event_date =
    CALCULATE (
        MAX ( VAccessControlLog[EventDate] ),
        FILTER (
            ALLSELECTED ( 'VAccessControlLog' ),
            'VAccessControlLog'[Name] = __name
        )
    )
VAR _status =
    IF (
        MAX ( 'VAccessControlLog'[EventDate] ) = __last_event_date
            && MAX ( 'VAccessControlLog'[DoorName] )
                IN { "PERSONEL TURNİKE 01 GİRİŞ", "PERSONNEL TURNER 02 ENTRANCE" },
        "Inside"
    )
RETURN
    _status

Best Regards,
Gao
Community Support Team

[koray]

Hi @v-cgao-msft 

It works great. Thank you very much.

Regards.