Unique Login Tracking

[HarveyM]

Hello,

I am having issues implementing a unique login tracker for a data set I have. Firsly, a unique login is classed as an individual users first login within a 24 hour time period. However the time periods do not align with days, the 24 hour period starts from the users first login, that is unique. For example I log in for the first time at 5pm, all logins for the next 24 hours are not unique. Then 2 days later I sign in at 9pm, then the next 24hrs of logins are not unique. 

Ideally the column would return a true if unique, a false if not.

Below is an example of the data.

[AlB]

Hi @HarveyM 

Where do you want this, in a calculated column?

Do you want to share some sample data with the expected result?

What would happen if you have a user login once every hour for three days in a row? What do you count as unique apart from the very first login?

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[HarveyM]

Hi @AlB 

Yes it would be good in a custom column.

Below is some exapmle data with the expected result.

So from the last login, every login until 07/07/2021 19.09 will not be unique as they fall into the last 24hr window.

In regards to your question, the frist login for each 24 hour period would be uniqe, the subsquent 23 for the rest of the day are not unique. In total you would have 3 unique logins.

[Jakinta · Accepted solution]

If you want it in M...

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUbLQN9Q3MlQwNLUyMFCK1UEVNMMmaA4TdEIStMCmEqugJaqgEVjQBJugKapFRti0G2NzkglY0ACLoBFEMBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [User = _t, Login = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    ChangedType = Table.TransformColumnTypes(#"Added Index",{{"Login", type datetime}}),
    #"Grouped Rows" = Table.Group(ChangedType, {"User"}, {{"Gr", each  Table.AddColumn(_, "Unique", each let 
l=List.Transform(ChangedType[Login], Number.From),
x=List.Accumulate(l, {l{0}}, (s,c)=> if c< List.Last(s)+1 then s else s&{c})
in 
List.Contains(x,Number.From([Login]))) , type table}}),
    #"Expanded Gr" = Table.ExpandTableColumn(#"Grouped Rows", "Gr", {"Index", "Login", "Unique"}, {"Index", "Login", "Unique"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Gr",{{"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
in
    #"Removed Columns"