Forum Discussion

Pappy54's avatar
Pappy54
Regular Visitor
2 years ago

Cannot Create Relationship in Data Model that joins more than one column

I am creating a data model in MS Fabric but need to be able to create a relationship that joins data from more than one field in the two tables.   For example, Table A has two fields named DataAreaID...
data warehouse
Fundamentals
one lake
power bi
AndyDDC's avatar
AndyDDC
Icon for Most Valuable Professional rankMost Valuable Professional
2 years ago

Hi Pappy54 yes as per power bi data modelling you're only able to create 1 active relationship between 2 tables. what I would normally do is (and I think you've tried this so could you screenshot?) create a column which concatenates the 2 base columns you're trying to join on.

 

The other alternative is to try and only use 1 base column if that's possible. Btw is this dynamics data?

Pappy54's avatar
Pappy54
Regular Visitor
2 years ago

Yes.  In D365 F&O, WhsInventTable is a support table to InventTable.  They are joined on DataAreaID and ItemID.  Unlike the Products table, there is no foreign key.   This could be problematic as we move forward with Synapse link to Dataverse since joining tables also requires that there be a relationship to dataareaid.  If tables are created in Fabric using Synapse Link, there is no way to create an index field that would serve as the primary key to join tables.  Please advise whether or not there are any other solutions.

  • AndyDDC's avatar
    AndyDDC
    Icon for Most Valuable Professional rankMost Valuable Professional
    2 years ago

    I'm not familiar with the WhsInventTable unfortuantely.  My suggestion would be to create Views in the lakehouse sql endpoint over the base tables that are created via the Synapse Link and CONCAT the DataReadId and ItemId columns.  The Views would then be exposed in the Model view and the concat column could then be used in the relationship.

     

    However, you would need to test read performance to see if it is satisfactory.