Forum Discussion
jswartz_09
Helper I
Helper IUnderstanding Dimensional Modeling
Hi everyone, I'm beginning my journey on trying to create a dimensional model for our Power BI environment. I come from an SQL background in reporting of just writing SQL queries for the end use...
Hi jswartz_09,
Here the error is happening because Education and Employment still have multiple rows per participant (1-* relationship). When you bring fields from all tables into a single visual, power bi cannot determine which Education or Employment record to use for each participant.
- To make this work, you must first reduce Education and Employment to one row per participant, for example the latest record so the relationships become 1-to-1 with participant. Once those relationships become 1-to-1, the visual will work without errors.
Thanks and regards,
Anjan Kumar Chippa
GeraldGEmerick
Memorable Member
Memorable Memberjswartz_09 Would it be possible to provide sample data for these tables? That would make it much quicker to mock up and give you an answer.
jswartz_09Helper I
Helper IOf course.
Here is the Participant Table:
| pk_Participant_Id | last_name | first_name | date_of_birth |
| 1 | Jones | George | 1/1/1955 |
| 2 | Bradshaw | Terry | 3/1/1975 |
| 3 | Smith | Bob | 10/1/2001 |
| 4 | Depp | Johnny | 7/1/1990 |
| 5 | Durham | Bull | 2/1/1980 |
| 6 | Lincoln | Abe | 11/15/1994 |
| 7 | Bass | Billy | 12/2/1988 |
| 8 | Khan | Shaka | 9/15/1964 |
| 9 | Streisand | Barb | 6/10/1989 |
| 10 | Connely | Jennifer | 4/24/1990 |
Case Table:
| pk_Case_ID | fk_Participant_Id | region | created_date |
| 1 | 3 | North | 1/1/2023 |
| 2 | 6 | South | 1/15/2023 |
| 3 | 7 | East | 1/16/2023 |
| 4 | 1 | West | 2/5/2023 |
| 5 | 4 | North | 4/1/2023 |
| 6 | 3 | North | 5/25/2023 |
| 7 | 6 | South | 7/29/2023 |
| 8 | 8 | West | 1/1/2024 |
| 9 | 2 | West | 2/10/2024 |
| 10 | 10 | East | 4/10/2024 |
| 11 | 5 | South | 5/1/2024 |
| 12 | 9 | North | 7/25/2024 |
| 13 | 5 | West | 8/1/2024 |
| 14 | 8 | North | 9/20/2024 |
| 15 | 1 | East | 11/15/2024 |
| 16 | 7 | East | 1/2/2025 |
| 17 | 2 | South | 1/15/2025 |
| 18 | 3 | North | 5/11/2025 |
| 19 | 10 | North | 8/12/2025 |
| 20 | 7 | West | 12/20/2025 |
Completion Table:
| pk_completion_id | fk_case_id | completion_date | completion_reason |
| 1 | 1 | 3/27/2023 | Unsuccessful |
| 2 | 2 | 6/15/2023 | Unsuccessful |
| 3 | 4 | 8/5/2023 | Successful |
| 4 | 5 | 10/1/2023 | Successful |
| 5 | 7 | 12/1/2023 | Unsuccessful |
| 6 | 3 | 1/16/2024 | Successful |
| 7 | 6 | 5/25/2024 | Successful |
| 8 | 8 | 7/1/2024 | Unsuccessful |
| 9 | 11 | 7/1/2024 | Unsuccessful |
| 10 | 9 | 8/10/2024 | Successful |
| 11 | 10 | 10/10/2024 | Successful |
| 12 | 12 | 1/1/2025 | Successful |
| 13 | 13 | 3/1/2025 | Successful |
| 14 | 15 | 5/15/2025 | Unsuccessful |
| 15 | 16 | 7/2/2025 | Successful |
| 16 | 14 | 9/1/2025 | Successful |
| 17 | 17 | 12/31/2025 | Successful |
Education Table:
| pk_Education_Id | fk_participant_id | education_level | created_date |
| 1 | 3 | High School | 1/1/2023 |
| 2 | 6 | High School | 1/15/2023 |
| 3 | 7 | Some College | 1/16/2023 |
| 4 | 1 | Bachelors | 2/5/2023 |
| 5 | 4 | High School | 4/1/2023 |
| 6 | 8 | Some College | 1/1/2024 |
| 7 | 2 | Bachelors | 2/10/2024 |
| 8 | 10 | Doctorate | 4/10/2024 |
| 9 | 5 | High School | 5/1/2024 |
| 10 | 9 | Some College | 7/25/2024 |
| 11 | 8 | Bachelors | 9/20/2024 |
| 12 | 1 | Masters | 11/15/2024 |
| 13 | 7 | Bachelors | 1/2/2025 |
| 14 | 3 | Some College | 5/11/2025 |
Employment Table:
| pk_Employment_Id | fk_participant_id | employment_status | created_date |
| 1 | 3 | unemployed | 1/1/2023 |
| 2 | 6 | part time | 1/15/2023 |
| 3 | 7 | part time | 1/16/2023 |
| 4 | 1 | full time | 2/5/2023 |
| 5 | 4 | self employed | 4/1/2023 |
| 6 | 6 | full time | 7/29/2023 |
| 7 | 8 | full time | 1/1/2024 |
| 8 | 2 | unemployed | 2/10/2024 |
| 9 | 10 | full time | 4/10/2024 |
| 10 | 5 | part time | 5/1/2024 |
| 11 | 9 | unemployed | 7/25/2024 |
| 12 | 7 | full time | 1/2/2025 |
| 13 | 2 | full time | 1/15/2025 |
| 14 | 3 | part time | 5/11/2025 |
| 15 | 10 | part time | 8/12/2025 |
In case those are hard to read here is some photos:
Participant:
Case:
Completion:
Employment:
Education: