Forum Discussion

JPNK's avatar
JPNK
Frequent Visitor
3 years ago
Solved

Count each occurrence

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 ne...
General Comment
  • JPNK's avatar
    JPNK
    3 years ago

    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's avatar
MarkLaf
Super User
3 years ago

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: