Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Report Help to Find Insiders

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.

 

NameSurnameDoorLast Event Date
AlexXEntrance Turnstile29.08.2023 11:48
AllyXExit Turnstile29.08.2023 10:18
JohnXExit Turnstile29.08.2023 07:00
TanyaXExit Turnstile29.08.2023 16:00
AlexXExit Turnstile29.08.2023 15:30
AllyXEntrance Turnstile29.08.2023 11:54
JohnXEntrance Turnstile29.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.

 

NameSurnameDoorLast Event DateStatus
JohnXEntrance Turnstile29.08.2023 15:00Inside
AllyXEntrance Turnstile29.08.2023 11:54Inside

 

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.

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous ,

    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

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous ,

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hello Anonymous 

       

      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
      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi Anonymous ,

        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