Countrows of all customer appointments using the contract start and end date from different table.

[Anonymous]

Dear all, 
My first post on the forum, so I hope I manage to get my question across. 
I'd like to calculate the number of appointments each customer makes within their contract. 

I've got two tables T_Appointments and T_Contracts, which are related through a customer table (T_Customer with unique customer numbers, not shown). 

T_Appointments 

Date_a	Customer_id	Type_a
15-06-2023	123	call
16-06-2023	123	visit
17-06-2023	402	call
19-06-2023	123	call
15-01-2024	402	call
20-01-2024	683	visit
12-02-2024	123	call
15-03-2024	123	call
06-04-2024	123	visit

T_Contracts (Contract details) 

Customer_id	Contract_id	Contract_start	Contract_end
123	125	01-01-2014	01-01-2015
123	357	15-06-2023	15-08-2023
123	915	01-01-2024	15-06-2024
402	471	01-01-2024	15-06-2024
683	120	01-01-2014	01-01-2015
683	472	01-01-2024	01-01-2025

-----------------------------------------------------------------------------------------------------------
To answer my question I tried to make a measure that would return:  

Contract_id	Appointment count
125	0
357	3
915	2
471	1
120	0
472	1

Or alternatively, create a new column in table_a that contains the contract_id corresponding to the customer and the date. 

Date_a	Customer_id	Type_a	Contract_id
15-06-2023	123	call	357
16-06-2023	123	visit	357
17-06-2023	402	call	null

The closest I could get was calculating the number of appointments for the the last contract. 
contract_appointment_count = SUMX(SUMMARIZE('T_customer','T_customer'[Customer_id],"_1",COUNTX(FILTER('T_Appointment','T_appointment'[Date_a] >= MAX(T_Contracts[Contract_start]) && 'T_Appointment','T_appointment'[Date_a] <= MAX(T_Contracts[Contract_end])),'Table_a'[customer_id])),[_1])

 
Thanks! 
Roos

[Anonymous · Accepted solution]

Hi @Anonymous ,

Based on my testing, please try the following methods again:

1.Create the simple tables.

2.Create the new measure to count rows.

Appointment_Count = 
VAR _filter = SELECTCOLUMNS(FILTER(CROSSJOIN(ALLSELECTED('T_Appointments'),ALLSELECTED('T_Contracts')),'T_Contracts'[Contract_start]<='T_Appointments'[Date_a]&&'T_Contracts'[Contract_end]>='T_Appointments'[Date_a]&&'T_Appointments'[Customer_id]='T_Contracts'[Customer_id]),"_CustomID",'T_Appointments'[Customer_id],"_contactID",'T_Contracts'[Contract_id])

RETURN
COUNTX(FILTER(_filter,[_contactID] = MAX('T_Contracts'[Contract_id])),[_contactID]) + 0

3.Drag the measure into the table visual. The result is shown below.

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.