Forum Discussion

dtoland's avatar
dtoland
New Member
4 years ago
Solved

Help with filtering from relationship across three tables

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!

   

Need Help
  • dtoland's avatar
    dtoland
    4 years ago

    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()
)

     

1 Reply

  • dtoland's avatar
    dtoland
    New Member
    4 years ago

    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()
)