topic Re: How to find unique login for a user? in DAX Commands and Tips

How to find unique login for a user?

amitpowerbi — Thu, 15 Dec 2022 14:22:19 GMT

Hello all,

I have an issue getting the DAX formula for the below case.

I will explain the case with an example.

Date	time	user id	unique login
15 Dec	7:30	amit	1
15 Dec	7:32	amit	0
15 Dec	7:40	amit	0
16 Dec	9:30	mohit	1
16 Dec	9:30	mohit	0
17 Dec	7:40	amit	1

In the above table as you can see unique login for amit appear once in the starting later on it should appear as 0 for the same date. because User "amit" has already counted as 1 in the beginning and in second row it has counted 0 because user already login for the day.

and same you can see for the other user mohit when is login freshly on 16 Dec it appeared as 1 and later if he logins it has to come as 0.

if anyone can help me with the DAX formula for this it would be highly appreciated.

Thank you in advance.

Re: How to find unique login for a user?

FreemanZ — Thu, 15 Dec 2022 14:26:02 GMT

Hi amitpowerbi

try to create a measure with this:

LoginCount =

COUNTROWS(

SUMMARIZE(

TableName,

TableName[Date],

TableName[User]

)

Re: How to find unique login for a user?

amitpowerbi — Thu, 15 Dec 2022 14:33:44 GMT

It is counting everything as one.

The repeated user for the day should count as a 0.

Anyway, I really thank you for your efforts.

Re: How to find unique login for a user?

FreemanZ — Thu, 15 Dec 2022 14:43:37 GMT

Hi amitpowerbi

Aha, then try to add a column with this:

UniqueLogin =

VAR _date = [Date]

VAR _user = [user id]

VAR _time =

MINX(

FILTER(TableName, TableName[Date]=_date&&TableName[user id]=_user),

TableName[time]

)

RETURN

IF([Time]=_time, 1, 0)

i tried and it worked like this:

Re: How to find unique login for a user?

amitpowerbi — Thu, 15 Dec 2022 16:26:41 GMT

Thank you so much.🙌