topic Re: Count people still present with table event in DAX Commands and Tips

Count people still present with table event

nicomano77 — Thu, 19 May 2022 09:12:09 GMT

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 🙂

Re: Count people still present with table event

Jihwan_Kim — Thu, 19 May 2022 12:17:04 GMT

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 )

Re: Count people still present with table event

nicomano77 — Fri, 20 May 2022 12:41:34 GMT

Hi ,

Sorry, next I will put pbix files .

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

Have a great day !

Re: Count people still present with table event

nicomano77 — Tue, 14 Jun 2022 13:34:54 GMT

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