Count matching ID's from one table to a another

[SeanPolley_Apty]

Problem:
I need to count how many matching ID numbers make it from Table 1 (Stage 1) to Table 2 (Stage 2) in order to calculate the conversion rate from Stage 1 to Stage 2.

Table 1 (Stage 1)

ID Value (string)	Stage
123A	Stage 1
456B	Stage 1
789C	Stage 1

Table 2 (Stage 2)

ID Value (string)	Stage
123A	Stage 2
999D	Stage 2
789C	Stage 2

In this example I would like to figure out my conversion rate from stage 1 to stage 2. How can I count that two ID's from Stage 1 made it to Stage 2 (123A and 789C)? Once I get the count which would be 2 in this case, I can take that and divide over the total ID count in Stage 2 to get a 2/3 or 66% conversion rate.

[PhilipTreacy · Accepted solution]

Hi @SeanPolley_Apty 

Download sample PBIX file

You can use INTERSECT to see which ID's are in both tables, then COUNTROWS of the table resulting from the INTERSECT

Measure = COUNTROWS(INTERSECT(VALUES(Table1[ID Value]),VALUES('Table2'[ID Value])))

Your conversion rate would then be

Conversion Rate = DIVIDE([Measure], COUNTROWS('Table2'))

Regards

Phil