Need a help with dax

[Krushnab85]

I have two tables -
Table 1 is Dimension which shows HCP Id with affiliated Center IDs 

Center ID	HCP ID
A	21
B	22
C	23
C	24
C	25
B	26

Table 2 Fact Table - Shows HCP reach with respect to Center ID 

Center ID	HCP ID
A	21
B	28
C	23
C	24
C	30
B	22

I want to calculate the reach count with respect to Center ID from Table 1
For example
From Table A for center ID C there are 3 HCPs i.e 23,24,25
and in Fact there are 23,24,30
so from dimension table 2 values are matching so it should return 2.

i need to solution over it.

[anmolmalviya05]

Hi @Krushnab85, Please try the below measure:

Reach Count =
CALCULATE(
COUNTROWS(Table1),
FILTER(
Table1,
Table1[HCP ID] IN VALUES(Table2[HCP ID])
)
)

[Tahreem24]

@Krushnab85 Try this also by creating a new table:

NewTable = INTERSECT(Table1,Table2)

[bhanu_gautam · Accepted solution]

@Krushnab85 First create a relationship between the two tables based on the Center ID column.

Reach Count =
VAR DimHCPs = VALUES('Table1'[HCP ID])
VAR FactHCPs = VALUES('Table2'[HCP ID])
RETURN
COUNTROWS(
INTERSECT(DimHCPs, FactHCPs)
)