Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jonclay
Helper IV
Helper IV

What is the best way to COUNT in this situation?

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

1 ACCEPTED SOLUTION
FreemanZ
Super User
Super User

hi @jonclay 

try like:

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

View solution in original post

4 REPLIES 4
jonclay
Helper IV
Helper IV

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

FreemanZ
Super User
Super User

hi @jonclay 

try like:

Count = 
COUNTROWS(
   FILTER(ALL(Table1), Table1[Name] IN VALUES(Table2[Name]))
)
olgad
Super User
Super User

olgad_0-1674599166730.png

 


DID I ANSWER YOUR QUESTION? PLEASE MARK MY POST AS A SOLUTION! APPRECIATE YOUR KUDO/LIKE!
PROUD TO BE A SUPER USER!
Best Stories, Interesting Cases: PowerBI Storytime Newsletter
Linkedin Profile: Linkedin
YouTube Channel: PowerBI Storytime
Padycosmos
Solution Sage
Solution Sage

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.