Forum Discussion
Filtered Count Between 2 Tables
I have two tables that look a little like this;
'Courses'
[Course_ID]
A
B
C
D
E
'Course Assignments'
[Course_ID]
A
C
D
B
B
E
A
I'm trying to calculate the number of assignments for each course in the 'Course' table so that I'd get
[Course_ID] [Assignment_Count]
A 2
B 2
C 1
D 1
E 1
My DAX for this won't work. It reads;
Assignment_Count =
COUNTA('Course Assignments'[ASSIGNMENT_ID]),FILTER('Course Assignments'[COURSE_ID]='Courses'[COURSE_ID])
Can anyone see what I'm doing wrong?
Thank you in advance.
Daniel.
You can create proper relationship between those two tables, then no measure is needed.
Alternatively, if no relationship, a measure as below works as well.
Assignment_Count = CALCULATE ( COUNTA ( 'Course Assignments'[ASSIGNMENT_ID] ), FILTER ( 'Course Assignments', 'Course Assignments'[ASSIGNMENT_ID] = LASTNONBLANK ( Courses[Course_ID], "" ) ) )See the attached pbix file.
2 Replies
- Eric_ZhangMicrosoft Employee
You can create proper relationship between those two tables, then no measure is needed.
Alternatively, if no relationship, a measure as below works as well.
Assignment_Count = CALCULATE ( COUNTA ( 'Course Assignments'[ASSIGNMENT_ID] ), FILTER ( 'Course Assignments', 'Course Assignments'[ASSIGNMENT_ID] = LASTNONBLANK ( Courses[Course_ID], "" ) ) )See the attached pbix file.
- DHBHelper V
Thank you, that's just what I need.