What is the best way to COUNT in this situation?

[jonclay]

Hi everyone

I'm sure this is pretty simple, but I just can't work it out!

My database has a Contact table containing 300,000 entries. This has been filtered down by various Transformations to around 100,000 entries. I have a spreadsheet that contains 100 entries. Both sets of data have been loaded into Power BI and joined by a relationship on the Name field. 

I simply want to count how many entries show on my report after the relationship to the two tables has been made i.e. those that match. The Contact table may contain several incidences of the same name, so I'm expecting the count to be around 300. However, no matter what I seem to do (COUNTROWS etc), I don't seem to come anywhere near this figure.

If I pull in the membership number field from the Contact table, I get 100,000 (so the full count of records after the various Transformations), and if I pull in the Name field from the spreadsheet I get a count of 100.

I'm sure that I'll kick myself when I get the answer to this!

Many thanks for your help.
Jon

[jonclay]

Thanks everyone. @FreemanZ answer worked for me in this particular situation.

[FreemanZ · Accepted solution]

hi @jonclay 

try like:

Count = 

COUNTROWS(

   FILTER(ALL(Table1), Table1[Name] IN VALUES(Table2[Name]))

)

[olgad]

[Padycosmos]

Hope this video would give an idea about the merging of tables :

https://www.youtube.com/watch?v=F5vzcm1RNNo&list=PLApPcvU5-R24K3mbxORV7T3ckVLfDjmHF&index=2