Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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).
Date | Low Active Users | Medium Active Users | High Active Users |
01/01/2019 | 23 | 10 | 5 |
02/01/2019 | 15 | 12 | 7 |
03/01/2019 | 45 | 30 | 25 |
04/01/2019 | 9 | 4 | 2 |
05/01/2019 | 56 | 16 | 11 |
06/01/2019 | 32 | 25 | 20 |
07/01/2019 | 15 | 10 | 5 |
08/01/2019 | 51 | 50 | 45 |
09/01/2019 | 46 | 36 | 31 |
10/01/2019 | 46 | 38 | 33 |
11/01/2019 | 22 | 20 | 15 |
12/01/2019 | 15 | 9 | 4 |
The login table looks like this:
ID | Date |
User1 | 01/01/2019 |
User2 | 01/01/2019 |
User3 | 01/01/2019 |
User4 | 01/01/2019 |
User5 | 01/01/2019 |
User6 | 01/01/2019 |
User7 | 01/01/2019 |
User4 | 02/01/2019 |
User5 | 02/01/2019 |
User6 | 02/01/2019 |
User7 | 02/01/2019 |
User2 | 03/01/2019 |
User3 | 03/01/2019 |
User4 | 03/01/2019 |
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
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
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |