Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |