Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello guys,
this is my first post and I need help.
I have a huge database with the columns: day, state, costumer code, SKU, origin, state and month.
I need to count how many times a costumer made a purchase in a month, but I need to mark when this code appeared that month. If it is the first time, mark with 1, the second with a 2, etc.
I can do that on Excel: =COUNTIFS($D$2:D10087;D10087;$E$2:E10087;E10087) where D is the Costumer Code and E is the Month. (I do that every line from D2 to D10087)
@rafarother , Output you want is not clear. Please check last post how to provide the information.
I think you need day rank. Try if this can work
rankx(filter(db, [Costumer Code] = earlier([Costumer Code]) && [Month] && earlier([Month])), db[Day],,asc,dense)
I want something like that:
But yes, I think i need a rank.
I can do it that way:
count_purchase =
COUNTROWS(FILTER(db, db[Customer Code] = EARLIER(db[Customer Code]) && db[Month] = EARLIER(db[Month]) && db[Day] <= EARLIER(db[Day])))
This works, but in a small database. In the huge database (21000000 lines), said I don´t have enough memory. (My work notebook have only 4GB RAM. Sad 😕 )
Please provide data with some expected results per instructions below.
How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingUser | Count |
---|---|
54 | |
23 | |
19 | |
18 | |
17 |
User | Count |
---|---|
89 | |
87 | |
50 | |
34 | |
22 |