Monthly occurencies, grouped by occurence

[Heursqem]

Hi

I have the following table [UserActivity]:

I am looking to create the following report:

Anywhone who can point me into the right direction?

Thanks in adavcne

Heursqem

[Fowmy]

@Heursqem 

Can you share some data in text format and clarify how to find about the users who have not used the report? do you have a separate table with Users?

[Heursqem]

Good Question!

Tye table itself contains all users. I want to count how many users have Logged in 0, once, twice etc per month.

Before users can login theu will have an entry in the same table, but with ActivityType "CREATED". So, all users will have at least 1 record in the table itself. And they will have a CREATED record before they are able to have a LOGIN record

Does that answer your question @Fowmy ?

[v-kkf-msft · Accepted solution]

Hi @Heursqem ,

Try the following measures:

Number of Users with 0 LOGINs = 
var tab = 
    SUMMARIZE(
        UserActivity, 
        UserActivity[UserGuID], 
        "_count", CALCULATE( COUNT(UserActivity[UserGuID]), UserActivity[ActivityType] = "LOGIN" )
    )
return COUNTROWS( FILTER(tab, [_count] = 0) )

Number of Users with 1 LOGINs = 
var tab = 
    SUMMARIZE(
        UserActivity, 
        UserActivity[UserGuID], 
        "_count", CALCULATE( COUNT(UserActivity[UserGuID]), UserActivity[ActivityType] = "LOGIN" )
    )
return COUNTROWS( FILTER(tab, [_count] = 1) )

Number of Users with 2 LOGINs = 
var tab = 
    SUMMARIZE(
        UserActivity, 
        UserActivity[UserGuID], 
        "_count", CALCULATE( COUNT(UserActivity[UserGuID]), UserActivity[ActivityType] = "LOGIN" )
    )
return COUNTROWS( FILTER(tab, [_count] = 2) )

Number of Users with 3 LOGINs = 
var tab = 
    SUMMARIZE(
        UserActivity, 
        UserActivity[UserGuID], 
        "_count", CALCULATE( COUNT(UserActivity[UserGuID]), UserActivity[ActivityType] = "LOGIN" )
    )
return COUNTROWS( FILTER(tab, [_count] = 3) )

Number of Users with 4 LOGINs = 
var tab = 
    SUMMARIZE(
        UserActivity, 
        UserActivity[UserGuID], 
        "_count", CALCULATE( COUNT(UserActivity[UserGuID]), UserActivity[ActivityType] = "LOGIN" )
    )
return COUNTROWS( FILTER(tab, [_count] = 4) )

Number of Users with 5+ LOGINs = 
var tab = 
    SUMMARIZE(
        UserActivity, 
        UserActivity[UserGuID], 
        "_count", CALCULATE( COUNT(UserActivity[UserGuID]), UserActivity[ActivityType] = "LOGIN" )
    )
return COUNTROWS( FILTER(tab, [_count] >= 5) )

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.