Count each occurrence

[JPNK]

Hello all, 

I've been trying for days to get my problem solved. I've looked at several tutorials, seen many YT videos, searched the Community Forum and nothing. There are similar things to what I need but they don't produce the result I need. I don't know if this is very specific, or if I just didn't use the correct key words in my search. Anyway, after days of searching and not finding anything, I'm posting here to see if anyone can help me.

I have this table in Power BI called login_list

   file_date        Option            email                                 sorted_list
10-Jan-2022        O1       [email protected]          [email protected]
10-Jan-2022        O2       [email protected]          [email protected]
10-Jan-2022        O1       [email protected]         [email protected]
10-Jan-2022        O1       [email protected]          [email protected]
02-Mar-2022       O1       [email protected]         [email protected]
02-Mar-2022       O2       [email protected]         [email protected]
02-Mar-2022       O2       [email protected]         [email protected]
02-Mar-2022       O1       [email protected]          [email protected]
06-Jul-2022         O1       [email protected]          [email protected]
06-Jul-2022         O1       [email protected]         [email protected]
06-Jul-2022         O1       [email protected]          [email protected]
06-Jul-2022         O2       [email protected]          [email protected]
06-Jul-2022         O2       [email protected]         [email protected]
06-Jul-2022         O2       [email protected]         [email protected]

login_list[file_date] = date of login

login_list[option] = system option of login

login_list[email] = user login name

login_list[sorted_list] = "just some concatenation of all the data in a single place"

Now the part that I'm trying to figure out is how to count the nth time a user has logged on, let me give you an example of what I mean, I want to create a Count column (see example below) that would have these type of results.

   file_date        Option            email                                  sorted_list                      Count
10-Jan-2022         O1       [email protected]         [email protected]         1
10-Jan-2022         O2       [email protected]         [email protected]         2
10-Jan-2022         O1       [email protected]        [email protected]        1
10-Jan-2022         O1       [email protected]         [email protected]          1
02-Mar-2022        O1       [email protected]        [email protected]       2
02-Mar-2022        O2       [email protected]        [email protected]       3
02-Mar-2022        O2       [email protected]        [email protected]       1
02-Mar-2022        O1       [email protected]         [email protected]        1
06-Jul-2022          O1       [email protected]         [email protected]          3
06-Jul-2022          O1       [email protected]        [email protected]         4
06-Jul-2022          O1       [email protected]         [email protected]           2
06-Jul-2022          O2       [email protected]         [email protected]          4
06-Jul-2022          O2       [email protected]        [email protected]         5
06-Jul-2022          O2       [email protected]        [email protected]         2

So as you can see from the login_list[Count] column, the value 1 means that this is the 1st time this user logged on, value 2 is 2nd time, 3 is 3rd, and so on and so forth.

At the end of the day I want to go to a specific line and say, for instance, that on July 6th the user [email protected] logged in the application for the 4th time or that bbb logged in for the 5th time and both used authentication method 2.

What is the simplest way to achieve this? (to get the login_list[Count] column)

Thank you 

[JPNK · Accepted solution]

Actually just figured out another way based on what @MarkLaf just said. It's not a one step method as you explained but I believe it's simpler to wrap your head around it.

So after trying to sort it unsuccessfully, I figured that the first thing to do was to actually sort the table correctly.

1 - I split the login_list[file_date] using the "/" delimiter and renamed the outputs to "file_date.year","file_date.month","file_date.day"

2 - Deleted the current  login_list[sorted_list] 

3 - Created a new login_list[sorted_list] in PowerQuery editor by concatenating login_list[file_date.year]&login_list[file_date.month]&login_list[file_date.day]&login_list[option]&login_list[email]

4 - Sorted by login_list[sorted_list]

6 - Added an index to the table

7 - Closed PowerQuery editor and in the data view I created the new "count" field:

count = CALCULATE(COUNT(login_list[email]),FILTER(login_list,login_list[email] = EARLIER(login_list[email]) && login_list[Index] <= EARLIER(login_list[Index])))

And it appears to be working...

Thanks for your suggestions

NOTE: props to @v-deddai1-msft for his suggestion on https://community.powerbi.com/t5/Desktop/Nth-Occurrence-of-ID-by-date-or-Index/td-p/1169286

[MarkLaf]

Nested join the tabe to itself, filter the nested tables based off criteria, then perform a row count on each filtered nested table:

let
    RelatedAll = Table.NestedJoin(login_list, "email", login_list, "email", "Count"),
    RelatedPrevious = 
    Table.ReplaceValue( 
        RelatedAll, 
        each [Count], 
        (row)=> Table.SelectRows(
            row[Count], 
            each [file_date] < row[file_date] 
            or [file_date] = row[file_date] and [Option] <= row[Option]
        ), 
        Replacer.ReplaceValue, 
        {"Count"} 
    ),
    Types = Value.ReplaceType( RelatedPrevious, Value.Type( RelatedAll ) ),
    CountRelatedPrevious = Table.TransformColumns(Types, {"Count", Table.RowCount, Int64.Type})
in
    CountRelatedPrevious

Output:

[mussaenda]

Hi @JPNK ,

I just dont understand the sorting of your rows.

on Jan 10, [email protected] is on 2nd row while  [email protected] is on 3rd row

but on 06 Jul, [email protected]  coame first before [email protected]  

I am able to create your request but I have the discrepancy on your count 

[JPNK]

Hi @mussaenda thanks for your feedback but the table isn't sorted. The data is as ingested from the csv files, the reason why the "sorted_list" was created was to sort the table later on.

[mussaenda]

Hi @JPNK ,

I just dont understand the sorting of your rows.

on Jan 10, [email protected] is on 2nd row while  [email protected] is on 3rd row

but on 06 Jul, [email protected]  coame first before [email protected]  

I am able to create your request but I have the discrepancy on your count 

[JPNK]

Hi @mussaenda thanks for your feedback but the table isn't sorted. The data is as ingested from the csv files, the reason why the "sorted_list" was created was to sort the table later on.