Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
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 November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.