Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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:
Solved! Go to 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))
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
@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))
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
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
16 | |
13 | |
12 | |
11 | |
11 |
User | Count |
---|---|
19 | |
14 | |
14 | |
11 | |
9 |