Forum Discussion

jswartz_09's avatar
jswartz_09
Helper I
6 months ago
Solved

Understanding 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...
Need Help
  • v-achippa's avatar
    v-achippa
    5 months ago

    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

v-achippa's avatar
v-achippa
Community Support
6 months ago

Hi jswartz_09,

 

Thank you for reaching out to Microsoft Fabric Community.

 

Thank you for the data. Based on the requirement here the Completion Table should be the fact table since the analysis is based on completed cases and completion date. Case and Participant Tables should be dimension tables. Education and Employment Tables must be reduced to one row per participant (latest record) before loading, otherwise power bi cannot determine which value to use.
With this model, filtering completion date for 2024–2025 and grouping by region will return the same result as the sql query.

 

Thanks and regards,

Anjan Kumar Chippa

v-achippa's avatar
v-achippa
Community Support
6 months ago

Hi jswartz_09,

 

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution I have provided for the issue worked? or let us know if you need any further assistance.

 

Thanks and regards,

Anjan Kumar Chippa