Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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 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"
Solved! Go to 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()
)
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()
)
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
86 | |
84 | |
83 | |
67 | |
49 |
User | Count |
---|---|
131 | |
111 | |
96 | |
71 | |
67 |