Count Occurrence of a data in another table to display in a table visual

[amabrenda1986]

I have two tables that are related 

Intake Table
Incident id	Date Created	Date Received	Incident Type	Date Completed
GRV-0001	4/1/2023	2/12/2022	Enquiry	6/15/2023
GRV-0002	1/23/2022	12/16/2021	Problem	5/12/2023
GRV-0003	9/18/2023	5/17/2023	Application
GRV-0004	3/25/2023	1/21/2023	Application
GRV-0005	4/12/2023	1/15/2022	Problem	7/10/2023
GRV-0006	9/15/2022	4/12/2022	Enquiry

Agreement Table
Agreement ID	Incident Id	Agreemt Type	created date
AGR-00001	GRV-0001	steward
AGR-00006	GRV-0001	utility
AGR-00007	GRV-0001	provincial
AGR-00002	GRV-0006	others
AGR-00003	GRV-0003	utility
AGR-00004	GRV-0003	provincial
AGR-00008	GRV-0005	steward
AGR-00009	GRV-0005	utility
AGR-00010	GRV-0005	steward

What I want in a Table Visual
Incident id	Number of associated Agreements
GRV-0001	3
GRV-0002	0
GRV-0003	2
GRV-0004	0
GRV-0005	3
GRV-0006	1

What measure or new column formula can i use to achieve what i want please?

[Ashish_Mathur]

Hi,

Create a many to One (Single) relationship from Table2 to Table1.  Drag IncidentID from Table1.  Write this measure

Measure = countrows(Areement[Agreement ID])

Hope this helps.

[parry2k]

@amabrenda1986 coz of the relationship.

[parry2k]

@amabrenda1986 add new measure and in a table visual , use incident id from intake table and this new measure:

Count = COUNTROWS ( AgreementTable ) + 0

[amabrenda1986]

it didn't work, because its counting the total number of agreements instead of counting the agreements associated with each id. Pleae look at the table again. 

appreciate your help

[v-cgao-msft · Accepted solution]

Hi @amabrenda1986 ,

Please try:

Number of associated Agreements = CALCULATE(COUNTROWS('Agreement'),'Agreement'[Incident Id]=MAX('Intake'[Incident id])) + 0

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[amabrenda1986]

how would this match to the respective ID in the table visual please?