Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Max_Faro
Frequent Visitor

Active Users from last 7 days (low, medium, highly active)

I'm trying to calculate how many users have been active the last 1-2 days in the last 7 days, the last 3-5 days in the last 7 days and 6-7 days in the last 7 days relative to a date. I have a table with all logins users made to work with. The expected result would be something like this where for each day you have the number of low active users(1-2 days), medium active users(3-5 days) and high active users(6-7 days).

 

DateLow Active UsersMedium Active UsersHigh Active Users
01/01/201923105
02/01/201915127
03/01/2019453025
04/01/2019942
05/01/2019561611
06/01/2019322520
07/01/201915105
08/01/2019515045
09/01/2019463631
10/01/2019463833
11/01/2019222015
12/01/20191594

 

The login table looks like this:

IDDate
User101/01/2019
User201/01/2019
User301/01/2019
User401/01/2019
User501/01/2019
User601/01/2019
User701/01/2019
User402/01/2019
User502/01/2019
User602/01/2019
User702/01/2019
User203/01/2019
User303/01/2019
User403/01/2019
3 REPLIES 3
v-shex-msft
Community Support
Community Support

HI @Max_Faro ,

You can use following calculated column formula to check current user active count in last 7 days and mark tag:

Active =
VAR _count =
    CALCULATE (
        COUNT ( Test[Date] ),
        FILTER (
            ALL ( Test ),
            [Date]
                >= EARLIER ( Test[Date] ) - 7
                && [Date] <= EARLIER ( Test[Date] )
                && [ID] = EARLIER ( Test[ID] )
        )
    )
RETURN
    IF (
        _count >= 6,
        "High",
        IF ( _count >= 3 && _count <= 5, "Medium", IF ( _count < 3, "Low" ) )
    )

After these steps, you can use date as row fields, active as column fields and 'count of id' as value to achieve your requirement.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi @v-shex-msft The problem here is that player can login many times per day and between this logins events are other events users perform. Therefore all users end up highlighted as High because of the number of logins they make each day.

Hi @Max_Faro ,

You can consider to apply distinct function on records to remove duplicate records. If you are confused on coding formula, please share pbix sample file and expected result for test.

 

Active =
VAR _count =
    CALCULATE (
        DISTINCTCOUNT ( Test[Date] ),
        FILTER (
            ALL ( Test ),
            [Date]
                >= EARLIER ( Test[Date] ) - 7
                && [Date] <= EARLIER ( Test[Date] )
                && [ID] = EARLIER ( Test[ID] )
        )
    )
RETURN
    IF (
        _count >= 6,
        "High",
        IF ( _count >= 3 && _count <= 5, "Medium", IF ( _count < 3, "Low" ) )
    )

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.