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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

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
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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