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_09Of 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: