cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nicomano77
Frequent Visitor

Count people still present with table event

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 🙂

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

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 )

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

3 REPLIES 3
nicomano77
Frequent Visitor

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
Super User
Super User

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 )

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


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

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Top Solution Authors
Top Kudoed Authors