Filtered Count Between 2 Tables

[DHB]

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.

[Eric_Zhang · Accepted solution]

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.

[DHB]

Thank you, that's just what I need.