Count people still present with table event

[nicomano77]

Hello everyone,

It should be simple but I'm a little confused

I have an event table ID_PERSON, CODE_EVENT (Arrival and Exit) , DATE_EVENT
I need to count the people still present with a contextual period filter.

If I filter by period I have people who arrived outside the selected period but are still present.

Thank you for your leads 🙂

[nicomano77]

Hi ,

Sorry,  next I will put pbix files .

Thanks a lot Jihwan_Kim it's so smart and really helpful !!!

Have a great day !

[Jihwan_Kim · Accepted solution]

Hi,

Please check the attached pbix file.

I am not sure if I understood your question correctly, because I could not know how your data model looks like, however, I tried to create a sample pbix file like the attached.

The DAX measure is in the attached pbix file.

Present ID count measure: =
VAR newtable =
    ADDCOLUMNS (
        DISTINCT ( 'ID'[ID] ),
        "@arrival", CALCULATE ( MAXX ( FILTER ( Data, Data[Event] = "Arrival" ), Data[Date] ) ),
        "@exit", CALCULATE ( MAXX ( FILTER ( Data, Data[Event] = "Exit" ), Data[Date] ) )
    )
VAR filternewtable =
    FILTER (
        newtable,
        [@arrival] <= MAX ( 'Calendar'[Date] )
            && [@exit] >= MIN ( 'Calendar'[Date] )
    )
RETURN
    COUNTROWS ( filternewtable )

[nicomano77]

Hi  😅,

Sorry again to ask another question but I still stuck .

In the case I need to have the second highest max arrival date How can i do that ? I tried with RANKX , TOPN without sucess, some advice in this case ? 

VAR newtable =
    ADDCOLUMNS (
        DISTINCT ( 'ID'[ID] ),
        "@arrival", CALCULATE ( MAXX ( FILTER ( Data, Data[Event] = "Arrival" ), Data[Date] ) ),
        "@exit", CALCULATE ( MAXX ( FILTER ( Data, Data[Event] = "Exit" ), Data[Date] ) )
    )

thanks again

Nicomano

---

@Jihwan_Kim wrote:

Hi,

Please check the attached pbix file.

I am not sure if I understood your question correctly, because I could not know how your data model looks like, however, I tried to create a sample pbix file like the attached.

The DAX measure is in the attached pbix file.

---

Nicomano