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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.