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.
The dau = DISTINCTCOUNT('behavior'[user_key])
The behavior table is related to a date dimension table, which has a date column.
the wau on 2019-06-03 should be the DISTINCTCOUNT of user_key from 2019-05-27 to 2019-06-02.
Any help would be appreciated!
EDIT:
I solved the problem by
Create a column "YearWeek" in the Date table that has the same value for each day within the same week (it could be a sequential integer or the date of the last day in the week).
Then you write:
weekly_active_user_cnt :=
CALCULATE (
DISTINCTCOUNT ( 'tb'[user_key] ),
ALL ( 'Date' ),
VALUES ( 'Date'[YearWeek] )
)
Idea from Marco Russo http://disq.us/p/2298ke7
Solved! Go to Solution.
Hi @booooosevic
It seems this problem is sloved.
I would suggest you to mark your answer as a solution so more people can find it.
@booooosevic wrote:EDIT:
I solved the problem by
Create a column "YearWeek" in the Date table that has the same value for each day within the same week (it could be a sequential integer or the date of the last day in the week).
Then you write:weekly_active_user_cnt :=
CALCULATE (
DISTINCTCOUNT ( 'tb'[user_key] ),
ALL ( 'Date' ),
VALUES ( 'Date'[YearWeek] )
)Idea from Marco Russo http://disq.us/p/2298ke7
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @booooosevic
It seems this problem is sloved.
I would suggest you to mark your answer as a solution so more people can find it.
@booooosevic wrote:EDIT:
I solved the problem by
Create a column "YearWeek" in the Date table that has the same value for each day within the same week (it could be a sequential integer or the date of the last day in the week).
Then you write:weekly_active_user_cnt :=
CALCULATE (
DISTINCTCOUNT ( 'tb'[user_key] ),
ALL ( 'Date' ),
VALUES ( 'Date'[YearWeek] )
)Idea from Marco Russo http://disq.us/p/2298ke7
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @booooosevic
You could try this measure
Measure = CALCULATE(DISTINCTCOUNT(Sheet3[user_key]),FILTER(ALLSELECTED('calendar'),'calendar'[Date]<=MAX('calendar'[Date])))
If it doesn't slove the problem, clear me about the following:
@booooosevic wrote:the wau on 2019-06-03 should be the DISTINCTCOUNT of user_key from 2019-05-27 to 2019-06-02.
user_key is active in 2019-5-27,28,29, 6-1,2
but inactive in 2019-5-30,31,
Should we include this user_key when calcuate the wau on 2019-06-03?
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Maggie:
Thanks for helping.
I'm sorry, but my description of expectations is not clear enough. In fact, we have a user behavior table(tb1) that records every behavior that a user has in our program.
The basic active user measure, active_user_cnt= DISCOUNTCOUNT('tb1'[user_key])
tb1:
user_key | date_key | time_key | session_id | page_key |
219805 | 7088 | 1439 | 0e540f02-b7db-46f2-b310-0cb5a66b40d4 | 48 |
219805 | 7088 | 1439 | 0e540f02-b7db-46f2-b310-0cb5a66b40d4 | 7 |
219805 | 7088 | 1438 | 0e540f02-b7db-46f2-b310-0cb5a66b40d4 | 13 |
219805 | 7088 | 1438 | 0e540f02-b7db-46f2-b310-0cb5a66b40d4 | 7 |
219805 | 7088 | 1437 | 0e540f02-b7db-46f2-b310-0cb5a66b40d4 | 3 |
219805 | 7088 | 1437 | 0e540f02-b7db-46f2-b310-0cb5a66b40d4 | 7 |
219805 | 7088 | 1437 | 0e540f02-b7db-46f2-b310-0cb5a66b40d4 | 4 |
219805 | 7088 | 1437 | 0e540f02-b7db-46f2-b310-0cb5a66b40d4 | 7 |
219805 | 7088 | 1436 | 0e540f02-b7db-46f2-b310-0cb5a66b40d4 | 4 |
219805 | 7088 | 1436 | 0e540f02-b7db-46f2-b310-0cb5a66b40d4 | 7 |
219805 | 7088 | 1435 | 0e540f02-b7db-46f2-b310-0cb5a66b40d4 | 7 |
287438 | 7088 | 1427 | 49a84088-26b5-4c5a-9fa5-50946e9cec29 | 42 |
287438 | 7088 | 1427 | 49a84088-26b5-4c5a-9fa5-50946e9cec29 | 19 |
287438 | 7088 | 1425 | 49a84088-26b5-4c5a-9fa5-50946e9cec29 | 19 |
287438 | 7088 | 1425 | 49a84088-26b5-4c5a-9fa5-50946e9cec29 | 42 |
The final effect I want is that:
date | daily_active_user_cnt | weekly_active_user_cnt |
2019/6/4 | 1417 | |
2019/6/3 | 1002 | |
2019/6/2 | 795 | 2839 |
2019/6/1 | 836 | 2839 |
2019/5/31 | 994 | 2839 |
2019/5/30 | 1023 | 2839 |
2019/5/29 | 967 | 2839 |
I could simply use a table visual with the active_user_cnt measure as the daily_active_user_cnt.
Now I'm wondering how to calculate the weekly_active_user_cnt.
You may notice that the weekly_active_user_cnt is the same number in 2019/5/27-2019/6/2, since they are in the same calendar week.
And on 6/3 & 6/4 they are blank because the current week has not ended yet.
As you mentioned about the user_key is active in 2019-5-27,28,29, 6-1,2 but inactive in 2019-5-30,31, we just ignore this. So the weekly_active_user_cnt is not equal to the sum of the 7 days dau. That's not a problem to us.
Please feel free to let me know if you still have any other confusions. Thanks in advance.
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 |
---|---|
110 | |
94 | |
80 | |
67 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |