Forum Discussion

Whiz's avatar
Whiz
New Member
3 months ago
Solved

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,   ...
  • Anonymous's avatar
    Anonymous
    3 months ago

    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.

mizan2390's avatar
mizan2390
Icon for Super User rankSuper User
3 months ago

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.