Forum Discussion
Need a help with dax
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.
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)
)
3 Replies
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)
)Hi Krushnab85, Please try the below measure:
Reach Count =
CALCULATE(
COUNTROWS(Table1),
FILTER(
Table1,
Table1[HCP ID] IN VALUES(Table2[HCP ID])
)
)
