How do I create a measure that checks two different tables and a date range from another table

[ilairdyi]

I am trying to create a measure that will check the condition if ID is in two different tables and is in the correct date range which is determined by a separate table?

Table 1 has a code, start date, and end date, and is used as a slicer to filter between different codes.

Code	Start Date	End Date
TD1	10/03/2022	24/03/2022
TD2	16/03/2022	30/03/2022
TD3	11/04/2022	25/04/2022

Table 2 "ID" has a code and ID. There can be only one unique ID to a code, but the same ID can be in different codes. For example, ID 1 is in TD1 and TD3. Table 1 "code" and Table 2 "ID" have a relationship through the code column, and Table 1 filters to Table 2.

Code	ID
TD1	1
TD1	5
TD1	2
TD1	8
TD2	3
TD2	4
TD2	6
TD3	1

Table 3 "Cust" has four columns: code, ID, name, and date. There is a slight problem with this table, as the code can be overwritten with the most recent code for a user. For example, ID 5 has a date between the date ranges of TD1, and as we can see in Table 2, this ID 5 was part of TD1. This is why I am going through the IDs to bring back the correct results/customer. There is a relationship from Table 1 "ID" to Table 3 "Cust" through the ID column, and it's a many-to-many relationship with a single filter to Table 3 "Cust".

code	ID	name	date
TD1	1	John	15/03/2022
TD66	5	Paul	18/03/2022
TD2	3	Samantha	16/03/2022
TD1	2	Mat	24/05/2022
TD2	3	Jane	28/03/2022

Table 4 "Trans" holds transactions and has three columns: ID, Amount, and date. There is a relationship from Table 2 "ID" to Table 4 "Trans" through the ID column, and it's a many-to-many relationship with a single filter.

ID	Amount	Date
1	10	15/03/2022
1	22	18/03/2022
5	70	20/09/2022
8	22	18/03/2022
4	100	20/03/2022
2	50	09/09/2022

This is my current set up and my current result

The ideal result would be to have the measure should $32 and would only should ID 1 because ID 1 is in Table 3 and Table 4 and within the correct date range from the slicer in Table 1 "code".

AS for the rest of the ID 5, 8  and 2  dont meet the criteria, for example we can see ID 2 and 5 are out of the date range that has been defined from the slicer. ID 8 should not show becuase it is not in table 3 how ever is shows in this measure which i want to change.

This  is my current set up for my model

ideal result would be both matrix and measure to look like this, but mainly to get the measure working correct.



Here is a copy of my Power Bi file sample.pbix 

[lbendlin]

Consider normalizing your Table 2.  As it is it will give you lots of trouble along the way.