Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello
I have a 6 moths data. It's web logins. I need a measure, where i could calculate HOW UNIQUE USERS LOGINS EVERY MONTH, and if this users logins every month - return count, and if not logins every moth - not return.
For example
user_id | login_date | web/app | |
1 | 2022-05-05 | web | |
2 | 2022-05-05 | web | |
3 | 2022-05-05 | web | |
4 | 2022-05-05 | app | |
1 | 2022-06-05 | app | |
5 | 2022-06-05 | web | |
3 | 2022-06-05 | app | |
1 | 2022-07-05 | web | |
6 | 2022-07-05 | web | |
3 | 2022-07-05 | web | |
1 | 2022-08-05 | app | |
6 | 2022-08-05 | web | |
7 | 2022-08-05 | web |
And this i need:
Date | Unique users all | Unique u. per period | login every month | |
month 5 | 4 | 4 | ||
month 6 | 5 | 3 | 2 | |
month 7 | 6 | 3 | 2 | |
month 8 | 7 | 3 | 1 |
Thank you. I not have idea. 😞
Solved! Go to Solution.
Hi, @Anonymous
You can try the following methods.
Unique users all = CALCULATE(MAX('Table'[user_id]),ALLEXCEPT('Table','Table'[login_date]))
Unique u. per period = CALCULATE(COUNT('Table'[web/app]),ALLEXCEPT('Table','Table'[login_date]))
Measure =
CALCULATE (
COUNT ( 'Table'[user_id] ),
FILTER (
ALL ( 'Table' ),
[user_id] = SELECTEDVALUE ( 'Table'[user_id] )
&& [login_date] <= SELECTEDVALUE ( 'Table'[login_date] )
)
)
login every month =
CALCULATE (
COUNT ( 'Table'[user_id] ),
FILTER (
ALL ( 'Table' ),
[Measure] >= 2
&& [login_date] = SELECTEDVALUE ( 'Table'[login_date] )
)
)
Is this the result you expect? If I misunderstand, please provide more explanation.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Anonymous
You can try the following methods.
Unique users all = CALCULATE(MAX('Table'[user_id]),ALLEXCEPT('Table','Table'[login_date]))
Unique u. per period = CALCULATE(COUNT('Table'[web/app]),ALLEXCEPT('Table','Table'[login_date]))
Measure =
CALCULATE (
COUNT ( 'Table'[user_id] ),
FILTER (
ALL ( 'Table' ),
[user_id] = SELECTEDVALUE ( 'Table'[user_id] )
&& [login_date] <= SELECTEDVALUE ( 'Table'[login_date] )
)
)
login every month =
CALCULATE (
COUNT ( 'Table'[user_id] ),
FILTER (
ALL ( 'Table' ),
[Measure] >= 2
&& [login_date] = SELECTEDVALUE ( 'Table'[login_date] )
)
)
Is this the result you expect? If I misunderstand, please provide more explanation.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
50 | |
23 | |
18 | |
18 | |
14 |
User | Count |
---|---|
91 | |
85 | |
46 | |
28 | |
21 |