Forum Discussion

DHB's avatar
DHB
Helper V
9 years ago
Solved

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_Zhang's avatar
    Eric_Zhang
    Microsoft 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.

    • DHB's avatar
      DHB
      Helper V

      Thank you, that's just what I need.