Forum Discussion
One to Many Relationship Problem
Hi dzakyramadani,
Thank you for reaching out to Microsoft Fabric Community.
Thank you cengizhanarslan and mh2587 for the prompt response.
Based on your description this is expected behavior. Even with a correct one to many relationship, power bi visuals will only show dimension rows that have matching fact rows when any column from the fact table is used in the visual or slicer.
Relationship direction controls only filter propagation. To keep all dimension rows visible, use only Table1 columns in rows and return fact data only through measures.Thanks and regards,
Anjan Kumar Chippa
Step 1
Go to Table1 in Data view and verify that each LOB-Division-Dept combination appears only once. If any combination repeats, remove or fix it. Table1 must be unique at this grain.
Step 2
Create the composite key in Table1 and in Table2 using the same columns, same order, and same data type. Check for extra spaces or mismatched text that could cause false differences.
Step 3
Open the model view and create the relationship from Table1’s composite key to Table2’s composite key. Set it as one-to-many with single direction from Table1 to Table2. If Power BI changes it back, return to Step 1 and fix duplicates in Table1.
Step 4
Create a measure in Table2 to return the value you want to show, such as color or any numeric value. Do not plan to use Table2 columns directly in visuals.
Step 5
Build the table visual using only columns from Table1 for rows. Add only the measure from Step 4 as the value.
Step 6
Add the Year slicer from Table2. Select different years and confirm that all rows from Table1 stay visible and only the measure values change or become blank.
Step 7
Confirm that no columns from Table2 are used anywhere in the visual, including tooltips or filters, because this will remove rows that do not exist in the fact table.
Step 8
If rows still disappear or the relationship flips again, recheck Table1 for duplicates and incorrect composite key values, then repeat the steps from the beginning.
no luck, ive ensured all the text are the same and even check with merge. all compositekey of the fact table have matches. but power bi still wont let me one to many. no duplicates no false differences. the same with my illustration of simple data i gave, it just dont work.
Kindly review both the upper and lower records. I encountered a similar issue previously, which was caused by the same record appearing in both places.
sorry, what do you mean by upper and lower record? i ensure all the composite key have no duplicate anywhere, all composite key on fact table exist in the table1, and even ensure all the building block of the compositekey type, and value is the same.
