Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
dtoland
New Member

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!

   

1 ACCEPTED SOLUTION
dtoland
New Member

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

 

View solution in original post

1 REPLY 1
dtoland
New Member

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

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.