Forum Discussion
Bridge Table Dax Help
Hi Whiz ,
The many to many relationship is occurring because Power BI requires the table on the one side of the relationship to contain unique values only. In this case, both BI Import and Actual contain repeated combinations of Project Name and Resource, so the original bridge table still could not enforce a proper one to many relationship.
The correct approach is to create a composite key by combining Project Name and Resource into a single column in both fact tables. Then create the bridge table using that same combined key so that the bridge contains only distinct values.
After creating the ProjectResourceKey column and rebuilding the relationships using that key, the model should display a one to many relationship from Bridge Fixed to both BI Import and Actual. This confirms that the bridge table is now functioning correctly as the unique dimension table and resolves the cardinality issue.
Please find the attached PBIX and Screenshort file for your reference.Thank you.
I appreciate the help, however when linking the bridge table to my fact tables it is still giving me a many to many relationships.
Hi Whiz ,
The many to many relationship is occurring because Power BI requires the table on the one side of the relationship to contain unique values only. In this case, both BI Import and Actual contain repeated combinations of Project Name and Resource, so the original bridge table still could not enforce a proper one to many relationship.
The correct approach is to create a composite key by combining Project Name and Resource into a single column in both fact tables. Then create the bridge table using that same combined key so that the bridge contains only distinct values.
After creating the ProjectResourceKey column and rebuilding the relationships using that key, the model should display a one to many relationship from Bridge Fixed to both BI Import and Actual. This confirms that the bridge table is now functioning correctly as the unique dimension table and resolves the cardinality issue.
Please find the attached PBIX and Screenshort file for your reference.
Thank you.
Hi Whiz ,
I hope the information provided has been useful. Please let me know if you need further clarification or would like to continue the discussion.
Thank you.Hi Whiz ,
I wanted to follow up and see if you had a chance to review the information shared. If you have any further questions or need additional assistance, feel free to reach out.
Thank you.