cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Syndicate_Admin
Administrator
Administrator

contar usuarios activos y nuevos usuarios a partir de eventos de registro

Tengo una tabla de eventos de inicio de sesión como esa

froxas_0-1634927394370.png

donde es id de evento, id de usuario y fecha.

Necesito contar los usuarios activos y los nuevos usuarios por período seleccionado.

nuevo usuario es el usuario que está en el período seleccionado y no en períodos anteriores.

El usuario activo es un usuario que se encuentra en período seletced y se puede encontrar en períodos anteriores

cómo puedo comprobar que el usuario de ID está en un período y otro y oposite.

1 ACCEPTED SOLUTION
Syndicate_Admin
Administrator
Administrator

#All Usr = CALCULATE(DISTINCTCOUNT(events[user_id]),REMOVEFILTERS())
#New Usr = 
COUNTROWS(
    FILTER(
        VALUES( events[user_id] ),
        NOT events[user_id]
            IN SELECTCOLUMNS(
                FILTER( ALL( events ), events[event date] < MIN( Calendar[Date] ) ),
                "@usr", events[user_id]
            )
    )
)
#Active Usr = 
COUNTROWS(
    FILTER(
        VALUES( events[user_id] ),
        events[user_id]
            IN SELECTCOLUMNS(
                FILTER( ALL( events ), events[event date] < MIN( Calendar[Date] ) ),
                "@usr", events[user_id]
            )
    )
)
#Lost Usr = 
CALCULATE(
    DISTINCTCOUNT( events[user_id] ),
    FILTER(
        SELECTCOLUMNS(
            FILTER( ALL( events ), events[event date] < MIN( Calendar[Date] ) ),
            "@usr", events[user_id]
        ),
        NOT [@usr] IN VALUES( events[user_id] )
    ),
    ALL()
)

Screenshot 2021-10-23 021806.png

View solution in original post

1 REPLY 1
Syndicate_Admin
Administrator
Administrator

#All Usr = CALCULATE(DISTINCTCOUNT(events[user_id]),REMOVEFILTERS())
#New Usr = 
COUNTROWS(
    FILTER(
        VALUES( events[user_id] ),
        NOT events[user_id]
            IN SELECTCOLUMNS(
                FILTER( ALL( events ), events[event date] < MIN( Calendar[Date] ) ),
                "@usr", events[user_id]
            )
    )
)
#Active Usr = 
COUNTROWS(
    FILTER(
        VALUES( events[user_id] ),
        events[user_id]
            IN SELECTCOLUMNS(
                FILTER( ALL( events ), events[event date] < MIN( Calendar[Date] ) ),
                "@usr", events[user_id]
            )
    )
)
#Lost Usr = 
CALCULATE(
    DISTINCTCOUNT( events[user_id] ),
    FILTER(
        SELECTCOLUMNS(
            FILTER( ALL( events ), events[event date] < MIN( Calendar[Date] ) ),
            "@usr", events[user_id]
        ),
        NOT [@usr] IN VALUES( events[user_id] )
    ),
    ALL()
)

Screenshot 2021-10-23 021806.png

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.