Help with filtering from relationship across three tables

[dtoland]

Our Organization is fairly new to PowerBI and I need some help with how to properly build out the data model to accomplish a requirement.

We have 3 Tables:

• Table A: Student Placement
• Table B: Student Record
• Table C: Enrollment 

Table A and Table C have 1:N relationships with Table B.

I am trying to build a report that returns the # of enrollments per program (countrow of Table C) where the Student Record (Table B) have at least 1 associated Placement (Table A) with column "Type" = "Job Placement"

I can return the total # of Placements that meet this requirement with this 

• Positive Placement = CALCULATE(COUNTROWS('Student Placements'),'Student Placements'[Type] = "Job Placement")

But I don't know that is needed or even helpful to this requirement since I haven't found a way to implement this into a measure that can be used in the Table C filter.

I'm not sure if I need to add a new calculated field on Table B to do a check against table A and then use that field to filter table C or if there is a way to build a measure on my _measures table to accomplish this requirement.

Any ideas to help point me in the right direction are appreciated!

[dtoland · Accepted solution]

I was able to achieve this goal by adding a column onto the enrollments table (Table C) that used the Filter() condition and the GUID of the related table B.

I also found I needed the date of the newest record in the rolated table so I added that logic with a MAXX and TOPN function (Thanks to @froxas on post https://community.powerbi.com/t5/Desktop/how-get-last-record-of-relation/m-p/887340#M425333)

Here's what the expression ended up like.

Positive Placement = 
if(
    COUNTROWS(
    FILTER('Student Placements',
           'Student Placements'[Student (c9_student)] = Enrollments[Student Name (c9_studentname)] &&
           SWITCH('Student Placements'[Type], 
                  "Job Placement", True, 
                  "Military", True, 
                  "Continuing Education", TRUE,
                  FALSE)
          )
  ) >0,
MAXX(
  TOPN(
    1,
    FILTER('Student Placements',
           'Student Placements'[Student (c9_student)] = Enrollments[Student Name (c9_studentname)] &&
           SWITCH('Student Placements'[Type], 
                  "Job Placement", True, 
                  "Military", True, 
                  "Continuing Education", TRUE,
                  FALSE)
          ),
    'Student Placements'[Start Date],DESC),
  'Student Placements'[Start Date]),
  BLANK()
)