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

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
suelaw1954
Frequent Visitor

Counting the number of rows in another table which have a certain value in a particular column

Hi,

I am analyzing timetabling for a University Faculty. The University's timetabling system exports a spreadsheet with the timetable details including the Teaching unit codes (alphanumeric) and the timetabled teaching activities (alphanumeric). As each teaching unit utilises multiple numbers of various types of teaching activity, there are multiple rows for each Teaching Unit.

I've set up a duplicate table and edited it in the Query editor so it is a single column list of the Units (1 row per Unit). I would like to set up an DAX function to count the number of rows in the first table in which the Unit code appears and deposit the number in the appropriate row in a new column of the second table.

 

I have tried using COUNTROWS with a FILTER to set up the function, but it just returns to total rowcount of table 1 in each row of table 2:

Unit Rows = COUNTROWS(FILTER(TT_FSE_SHFYR_2021_PRACS, 'TT_FSE_SHFYR_2021_PRACS'[Unit]=[Unit]))
I would appreciate some help in this.
Thanks in advance,
Sue
1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@suelaw1954 Perhaps try:

Unit Rows = 
  VAR __Unit = [Unit] //unit column in 2nd table (destination)
RETURN
  COUNTROWS(FILTER('TT_FSE_SHFYR_2021_PRACS', 'TT_FSE_SHFYR_2021_PRACS'[Unit]=__Unit))


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

3 REPLIES 3
suelaw1954
Frequent Visitor

@Greg_Deckler 

Yes, thanks.

Just to clarify the logic, in order to compare the alphanumeric value of a cell with the content of a cell in another table I have to create a variable whose value is equal to the cell value.

Sue

Greg_Deckler
Super User
Super User

@suelaw1954 Perhaps try:

Unit Rows = 
  VAR __Unit = [Unit] //unit column in 2nd table (destination)
RETURN
  COUNTROWS(FILTER('TT_FSE_SHFYR_2021_PRACS', 'TT_FSE_SHFYR_2021_PRACS'[Unit]=__Unit))


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler 

Just looking up your books in our library and the first chapter of "DAX Cookbook" describes what I try to do when starting in yet another bit of software: "Thinking in DAX". Too few introductions cover the logic behind a language.

Sue

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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