topic Re: DISTINCTCOUNT if id each every month. in DAX Commands and Tips

DISTINCTCOUNT if id each every month.

Anonymous — Fri, 26 Aug 2022 11:38:30 GMT

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. 😞

Re: DISTINCTCOUNT if id each every month.

daXtreme — Fri, 26 Aug 2022 13:37:04 GMT

Re: DISTINCTCOUNT if id each every month.

v-zhangti — Tue, 30 Aug 2022 06:05:27 GMT

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.