Forum Discussion
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 and ItemID Table B also has DataAreaID and ItemID. I need to be able to create a relationship between Table A and Table B but the Fabric Relationship Editor only lets me join on one field. I also tried creating Measures, concatenating the columns on both tables to create an index column then creating a relationship on the measures but the Relationship Editor does not show the measures to link them.
Help!!! This this functionality is not available on the data model, this is a huge miss.
6 Replies
- AndyDDC
Most Valuable Professional
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?
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
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.
Creating a view is exactly what I did. I concatenated the dataareaid field with the itemid, naming it IDX_DataAreaIdItemId. I was trying to avoid this approach but it appears that I will have no choice. I know that Fabric is a work in progress but for any business that has a multi-company instance of D365 F&O, including the dataareaid in your joins is a must. Until there is multi-column functionality with regard to table relations within the data model, views will need to be created for all of our fact tables and many dimension tables, such as the item table indicated above. Thank you very much for your quick response.
v-cboorla-msftMicrosoft Employee
Hi Pappy54
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet .
In case if you have any resolution please do share that same with the community as it can be helpful to others .
Otherwise, will respond back with the more details and we will try to help .
Thanks.- v-cboorla-msft
Hi Pappy54
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others.
