Forum Discussion

nicomano77's avatar
nicomano77
Frequent Visitor
4 years ago
Solved

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 🙂

DAX EVENT TABLE
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    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 )

3 Replies

  • nicomano77's avatar
    nicomano77
    Frequent Visitor
    4 years ago

    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's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      nicomano77
      Frequent Visitor
      4 years ago

      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