Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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()
)
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
114 | |
90 | |
84 | |
76 | |
64 |
User | Count |
---|---|
135 | |
112 | |
98 | |
98 | |
92 |