Match non-unique ID from one table with multiple matches from another table

[Anonymous]

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

[Anonymous]

1. Thank you for your reply. I can't open this file since my Power BI version is older. I also got an error message saying that "The value for 'Customer ID' cannot be determined. Either the column doesn't exist, or there is no current row for this column." (assuming your suggestion is to create a measure). I updated the sample data to ensure there are at least two risk alerts for the same loan applications. Not sure if this is needed, but I cannot create a relationship between the two data tables since they do not have unique IDs. 

[lbendlin]