Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I have a list of payment attempts, and I'd like to add in the attempt number for each row. A "string" of attempts would be where the date is equal to or is the next day. When you get to a day where this is not the case, the counter needs to "reset". This would be for each ID. So there should be a reset per ID as well as the break in the date string.
For example, for the data below I would like the final data to look something like this, starting only with the first two columns:
Initially I created a "unique ID" by combining the month number and the ID, and grouped by this ID to get the count, but I'd like to be able to assign the attempt number to each date and this also didn't work for attempt strings that went over two months.
Just can't figure it out, any help would be welcome.
Solved! Go to Solution.
Hi @Anonymous
You may add a index column first. Then you may get the rank with below dax.
Column = IF ( Table1[DateCreated] = LOOKUPVALUE ( Table1[DateCreated], Table1[Index], Table1[Index] - 1 ) || Table1[DateCreated] = LOOKUPVALUE ( Table1[DateCreated], Table1[Index], Table1[Index] - 1 ) + 1, 0, 1 )
Column 2 = SUMX ( FILTER ( Table1, Table1[Index] <= EARLIER ( Table1[Index] ) ), Table1[Column] )
Rank = RANKX ( FILTER ( Table1, Table1[Column 2] = EARLIER ( Table1[Column 2] ) ), Table1[Index], , ASC )
Regards,
Cherie
Hi @Anonymous
You may add a index column first. Then you may get the rank with below dax.
Column = IF ( Table1[DateCreated] = LOOKUPVALUE ( Table1[DateCreated], Table1[Index], Table1[Index] - 1 ) || Table1[DateCreated] = LOOKUPVALUE ( Table1[DateCreated], Table1[Index], Table1[Index] - 1 ) + 1, 0, 1 )
Column 2 = SUMX ( FILTER ( Table1, Table1[Index] <= EARLIER ( Table1[Index] ) ), Table1[Column] )
Rank = RANKX ( FILTER ( Table1, Table1[Column 2] = EARLIER ( Table1[Column 2] ) ), Table1[Index], , ASC )
Regards,
Cherie
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |