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

     

JPNK's avatar
JPNK
Frequent Visitor
3 years ago

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.