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 have a user activity tables that records every time a user opens that. It displays the user's ID and exactly when they opened the application (date).
Sample data:
user_id | date |
1 | 01/01/2021 |
2 | 01/01/2021 |
2 | 02/01/2021 |
3 | 03/01/2021 |
So from the sample data, on 02/01/2021, only user 2 is retained since he opened the app the day before and on 02/01/2021.
The output will be visualized in a stacked column chart where the active users and the users retained on a particular day are highlighted (stacked on top of each other). Something like this:
I want to calculate the number of unique users that have been retained from previous day, i.e, the users that opened that app the day before a date then again on that date.
How would I do that? It can be either a measure or calculated column.
Solved! Go to Solution.
@Anonymous
Active Users Measure:
Active Users = COUNT(Table3[user_id])
Retained Users Measure:
Retained Users =
VAR __CurrentDate = MAX(Table3[date])
VAR __CurrentUsers = VALUES(Table3[user_id])
VAR __PrevDate = CALCULATE( MAX(Table3[date]) , Table3[date] < __CurrentDate )
VAR __PrevDayUsers = CALCULATETABLE( VALUES(Table3[user_id]) , Table3[date] = __PrevDate )
VAR __Result = COUNTROWS( INTERSECT( __CurrentUsers , __PrevDayUsers ) )
RETURN
__Result
Result in Chart:
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Anonymous
Active Users Measure:
Active Users = COUNT(Table3[user_id])
Retained Users Measure:
Retained Users =
VAR __CurrentDate = MAX(Table3[date])
VAR __CurrentUsers = VALUES(Table3[user_id])
VAR __PrevDate = CALCULATE( MAX(Table3[date]) , Table3[date] < __CurrentDate )
VAR __PrevDayUsers = CALCULATETABLE( VALUES(Table3[user_id]) , Table3[date] = __PrevDate )
VAR __Result = COUNTROWS( INTERSECT( __CurrentUsers , __PrevDayUsers ) )
RETURN
__Result
Result in Chart:
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
This worked exactly like I wanted. Much appreciated.
Just one question; in the formula for the previous date, would just subtracting 1 from the current date return the same thing?
@Anonymous
Yes, you can do but I did it this way if you do not have data for the previous day, it gets the last available date
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Anonymous
Can you share some sample data with the desired output to have a clear understanding of your question?
Mention whether you want a calculated column or measure.
You can either paste your data in the reply box or save it in OneDrive, Google Drive, or any other cloud-sharing platform and share the link here.
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
I updated my post. Hope it's clearer.
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 |
---|---|
107 | |
98 | |
78 | |
65 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |