The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
Hi I got a complicated problem and would appreciate any help/advice on how to approach it: Table 1 contains denied loan applications and Table 2 contains all risk alerts associated with certain custmers. Each risk alert covers certain time period with a start and end date. My task is to find out how many denied applications had active alerts in each denied date, and count the number of active alerts by alert type (delinquency, loss, security, etc.). Account # is unique but Customer ID is not. Each customer can have multiple loan applications and multiple risk alerts.
Sample date:
Table 1
Account # | Customer ID | Denied Date | Denied Reason |
101 | 1234 | 10/20/2019 | A |
123 | 2356 | 08/23/2020 | B |
234 | 7789 | 02/23/2021 | C |
546 | 1234 | 08/23/2020 | A |
Table 2
Customer | Alert Type | Start Date | End Date |
1234 | Delinquency | 07/20/2019 | 07/20/2019 |
2356 | Loss | 08/23/2020 | 08/29/2020 |
7789 | Security | 02/23/2021 | 02/28/2021 |
1234 | Fraud | 08/22/2019 | 08/24/2020 |
1234 | Litigation | 08/20/2019 | 08/23/2020 |