Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I've have a data set that I've been really struggling to summarize and create a report that is easy to understand. I basically have two tables, one is Customer which has all the customer details and the other is the "Activities" which stores the activities done on their account (i.e notes on their account, emails and reminders that have been sent). They're linked on the Customer ID field. I'm working on a query to identify if the system has sent them two reminders and the date that the most recent reminders were sent.
Below is an example of the data. I added the two custom columns "60 Day Sent" and "30 Day Sent" to narrow down only the reminder emails sent by the system.
Customer ID | Subject | Date Sent | 60 Day Sent | 30 Day Sent |
C243334 | 60-day notice to renew your course: C243334 | 2022-03-05 | TRUE | FALSE |
C243334 | Course will expire in 30 days: C243334 | 2022-04-05 | FALSE | TRUE |
C243334 | 60-day notice to renew your course: C243334 | 2021-03-05 | TRUE | FALSE |
C243334 | Course will expire in 30 days: C243334 | 2021-04-05 | FALSE | TRUE |
C89098 | 60-day notice to renew your course: C89098 | 2022-09-04 | TRUE | FALSE |
C3454 | 60-day notice to renew your course: C3454 | 2022-08-03 | TRUE | FALSE |
C3454 | Course will expire in 30 days C3454 | 2022-09-03 | FALSE | TRUE |
C775592 | Course will expire in 30 days C775592 | 2022-07-15 | FALSE | TRUE |
C09348 | 60-day notice to renew your course: C09348 | 2021-06-14 | TRUE | FALSE |
C09348 | 60-day notice to renew your course: C09348 | 2022-06-14 | TRUE | FALSE |
Where I'm stuck is creating a table or report that summarises if we've sent reminders and the date of the most recent reminder of each type. For example for customers C243334 and C09348, I only want to see the reminders we sent them in 2022 since those are the most recent. If we haven't sent anything the value can either be No or Blank. This would be my desired result:
Customer ID | 60 Day Reminder | Date Sent | 30 Day Reminder | Date Sent |
C243334 | Yes | 2022-03-05 | Yes | 2022-04-05 |
C89098 | Yes | 2022-09-04 | ||
C3454 | Yes | 2022-08-03 | Yes | 2022-09-03 |
C775592 | Yes | 2022-07-15 | ||
C09348 | Yes | 2022-06-14 |
Solved! Go to Solution.
File attached.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
User | Count |
---|---|
69 | |
36 | |
21 | |
17 | |
15 |
User | Count |
---|---|
126 | |
30 | |
28 | |
24 | |
22 |