Forum Discussion

HarveyM's avatar
HarveyM
Icon for Helper I rankHelper I
5 years ago
Solved

Unique Login Tracking

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 ti...
Need Help
Tutorial Requests
  • Jakinta's avatar
    Jakinta
    5 years ago

    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"
AlB's avatar
AlB
Icon for Community Champion rankCommunity Champion
5 years ago

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's avatar
    HarveyM
    Icon for Helper I rankHelper I
    5 years ago

    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's avatar
      Jakinta
      Icon for Solution Sage rankSolution Sage
      5 years ago

      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"