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.
Hey all,
I am looking to create a new column that will calculate the running total per unique identifier with multiple conditions.
Context: The table I am using is of Gift Card (GC) activations and redemptions. A new row is created each time a new action is logged. All unique GCs have one "Register" but can have multiple actions ( "Increment", "Redeem", "Forced Redeem").
Each time a unique card is redeemed ("Redeem" || "Forced Redeem") I want the running total of the number of redemptions to increase.
For this sample, I have created four scenarios:
Scenario 1) Serial # 1234 -- Two redemptions on different days
Scenario 2) Serial # 5678 -- Three redemptions on different days
Scenario 3) Serial # 9123 -- Two redemptions on the same day, different times
Scenario 4) Serial # 4567 -- No redemption
Desired result in shown in the last column of the table:
Serial Number | Action | Action Date | Action Time | Register Date | Desired Result: Count of Redeem/Forced redeems |
1234 | Register | 12-May-21 | 12:00:00 AM | 12-May-21 | |
1234 | Redeem | 14-May-21 | 1:00:00 AM | 12-May-21 | 1 |
1234 | Redeem | 16-May-21 | 2:00:00 AM | 12-May-21 | 2 |
5678 | Register | 1-May-21 | 12:00:00 AM | 1-May-21 | |
5678 | Increment | 2-May-21 | 4:00:00 AM | 1-May-21 | |
5678 | Redeem | 3-May-21 | 5:00:00 AM | 1-May-21 | 1 |
5678 | Forced Redeem | 4-May-21 | 6:00:00 AM | 1-May-21 | 2 |
5678 | Forced Redeem | 5-May-21 | 7:00:00 AM | 1-May-21 | 3 |
9123 | Register | 1-Jan-21 | 8:00:00 AM | 1-Jan-21 | |
9123 | Forced Redeem | 1-Jan-21 | 9:00:00 AM | 1-Jan-21 | 1 |
9123 | Forced Redeem | 1-Jan-21 | 10:00:00 AM | 1-Jan-21 | 2 |
4567 | Register | 1-Feb-21 | 8:00:00 AM | 1-Feb-21 |
I am really trying to learn how DAX is working so I would very much appreciate an explanation of the logic behind the proposed solution. I also wonder about the difference between doing this step in Power Query vs the report. What is the best practice that in terms of performance?
Thank you very much for your suggestions!
C
Solved! Go to Solution.
@CGodfrey you can also use RANK function, try following two columns:
Action Date Time = Action Date Time = GC[Action Date] + GC[Action Time]
Rank =
IF (
GC[Is Redeemed] = "Yes",
RANKX (
FILTER (
ALL ( GC[Serial Number], GC[Action Date Time], GC[Action] ),
GC[Serial Number] = EARLIER ( GC[Serial Number] ) &&
SEARCH ( "Redeem", GC[Action], , -1 ) <> -1
),
GC[Action Date Time]
, , ASC
)
)
✨ Follow us on LinkedIn
Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@CGodfrey you can also use RANK function, try following two columns:
Action Date Time = Action Date Time = GC[Action Date] + GC[Action Time]
Rank =
IF (
GC[Is Redeemed] = "Yes",
RANKX (
FILTER (
ALL ( GC[Serial Number], GC[Action Date Time], GC[Action] ),
GC[Serial Number] = EARLIER ( GC[Serial Number] ) &&
SEARCH ( "Redeem", GC[Action], , -1 ) <> -1
),
GC[Action Date Time]
, , ASC
)
)
✨ Follow us on LinkedIn
Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Hey @parry2k This solution has worked! I am struggling to understand what precisely is happening but good opportunity to wrap my head around RANKX. Thank you for your assistance!
@CGodfrey Try:
Column =
VAR __SerialNumber = [Serial Number]
VAR __Date = [Action Date]
RETURN
COUNTROWS(FILTER('Table',[Serial Number] = __SerialNumber && [Action Date] <= __Date && [Action] = "Redeem"))
@Greg_Deckler That certainly worked for the "Redeem" action. How could I make it to evaluate for both "Redeem" OR "Foreced Redeem"?
Also, in Scenario 3 above the GC is redeemed twice in the same day so it would have to then look at the Action Time to know which came first that day.
@CGodfrey Well the first one would be:
Column =
VAR __SerialNumber = [Serial Number]
VAR __Date = [Action Date]
RETURN
COUNTROWS(FILTER('Table',[Serial Number] = __SerialNumber && [Action Date] <= __Date && ([Action] = "Redeem") || [Action] = "Forced Redeem"))
For the second one, I would recommend creating a new calculated column in Power Query that combines your Date and Time into a single Datetime column and use that instead of [Date] in your formula.
@Greg_Deckler I switched to the date/time combo which resolved some of the errornous results; however, it still evaluates actions that are not equal to "Redeem" or "Forced redeem" and assigns them an additonal value. More specifically, when the action = "Increment" which is very off as I'd expect that would be filtered out with the
&& ([Action] = "Redeem") || [Action] = "Forced Redeem"))
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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |