Forum Discussion
Bridge Table Dax Help
I am looking for help with creating a bridge table and the DAX with combining two tables with the same two columns. Please help as this has been a difficult issue for me to resolve. Regards,
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.
9 Replies
In linea 4, 5 and 6 you are missing a closing round parenthesis
If this helped, please consider giving kudos and mark as a solution
me in replies or I'll lose your thread
Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page
Consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
I appreciate your assistance, however now it is still giving me a many to many cardinality. Any suggestions?
Create a table with DAX below
Bridge =DISTINCT(UNION(SELECTCOLUMNS('BI Import',"Project Name", 'BI Import'[Project Name],"Resource", 'BI Import'[Resource]),SELECTCOLUMNS(Actual,"Project Name", Actual[Project Name],"Resource", Actual[Resource])))Thanks for the assistance, however it is still giving me a Many to Many Cardinality.
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
To enforce a One-to-Many relationship, you must create a single, unique identifier—a Composite Key—by concatenating the two columns.
Before creating the bridge table, both BI Import and Actual need a calculated column to serve as the relationship key.ProjectResourceKey = 'BI Import'[Project Name] & "|" & 'BI Import'[Resource]
Now, generate the bridge table using DAX, ensuring the new composite key is included and unique.
Bridge=
DISTINCT (
UNION (
SELECTCOLUMNS (
'BI Import',
"Project Name", 'BI Import'[Project Name],
"Resource", 'BI Import'[Resource],
"ProjectResourceKey",
'BI Import'[Project Name] & "|" & 'BI Import'[Resource]
),
SELECTCOLUMNS (
Actual,
"Project Name", Actual[Project Name],
"Resource", Actual[Resource],
"ProjectResourceKey",
Actual[Project Name] & "|" & Actual[Resource]
)
)
)Now, draw the relationship from Bridge[ProjectResourceKey] to BI Import[ProjectResourceKey], and then from Bridge[ProjectResourceKey] to Actual[ProjectResourceKey]
If this helps you, please mark this as solution and give me a kudo.
@me so that, I dont loose your thread.
