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

[suelaw1954]

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

[suelaw1954]

@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 · Accepted solution]

@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))

[suelaw1954]

@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