Forum Discussion

KatkaS's avatar
KatkaS
Post Patron
5 years ago
Solved

Keep first value

Hello,

 

I have a simple excel sheets with names and dates when they logged in an application.

I need to keep (count) only their first log-in, delete the rest (or keep, but just count the first login).

 

Result will be a stacked column chart: Number of log-ins and month+year.

 

Could anyone please help?

Thank you!!

Name Logged in Result
Anna26/8/2020keep
Anna29/8/2020delete
Albert01/08/2020keep
Anna31/8/2020delete
Cecile01/01/2020keep
  • fhill's avatar
    fhill
    5 years ago

     

    In Query Editor, try the Group By Feature -> Advanced to Select the MIN Login (by Email) and COUNT of Rows.

     

    After that, you can take the GROUPED DATA into a simple chart.

     

     

7 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    KatkaS , not very clear. Do we need to calculate first log in (Logic)

     

    calculate(countrows(table),filter(table, table[Result] in{"delete","First log in"}))

    • KatkaS's avatar
      KatkaS
      Post Patron
      5 years ago

      Thank you, Amit. I tried your measure, but I don't understand what you mean by in{"delete","First log in"})) - do I need to add new column "First log in"..?

      And yes, we need to count how many new logins were every month - month are on X axis of a stacked column chart..

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    5 years ago

    KatkaS - Seems like:

    Measure =
  VAR __Month = MAX('Calendar'[Month])
  VAR __Year = MAX('Calendar'[Year])
  VAR __Table = SUMMARIZE(ALL('Table'),[Name],"First",MIN([Logged in]))
RETURN
  COUNTROWS(FILTER(__Table,YEAR([First])=__Year && MONTH([First])=__Month))
    • KatkaS's avatar
      KatkaS
      Post Patron
      5 years ago

      Thank you, Greg, but I'm afraid this is too advanced for me. You mentioned MAX('Calendar'[Month])

       - do I need to create new table with periods and create relationship between the User login table..? 

  • fhill's avatar
    fhill
    Resident Rockstar
    5 years ago

     

    In Query Editor, try the Group By Feature -> Advanced to Select the MIN Login (by Email) and COUNT of Rows.

     

    After that, you can take the GROUPED DATA into a simple chart.

     

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    5 years ago

    Hi,

    This M code works

    let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Logged in", type datetime}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Name", Order.Ascending}, {"Logged in", Order.Ascending}}),
    Partition = Table.Group(#"Sorted Rows", {"Name"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Logged in", "Index"}, {"Logged in", "Index"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Partition", "Action", each if [Index]=1 then "Keep" else "Delete"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"})
in
    #"Removed Columns"

    If you do not want to Load the "Delete" rows to your Data Model, then you can simply filter out those values.

    Hope this helps.