Forum Discussion
Relationship issue with composite data model in Power BI
I've prepared on composite model with one DQ table and one import mode table and connected both the tables as one to many from import to DQ mode table. here, as shown in image, order table is in DQ mode and timezone table is in import and it is excel sheet coming from sharepoint folder.
Can anyone explain, why the blank brackets are coming before relation ship cardinality?
Also I'm trying to create one calculated field in order table using related dax but it is throwing error that relationshio isn't define between tables. If anyone can help. Thanks in advance!
Relationship between import mode tables from any source = strong relationship = RELATED() is supported
Relationship between Direct Query tables from same SQL database = strong relationship = RELATED() is supported
Relationship between any import mode table and any Direct Query table = limited relationship = RELATED() is not sopported
Relationship between two Direct Query tables from different sources/databases = limited relationship= RELATED() is not supported
Your situation that is throwing the issue is the 3rd case.
Your situation that works is the first case.
Reason: Functionality as provided by Microsoft.
4 Replies
Hi Krupa_Parmar ,
The relationships with the brackets are weak relationships or "limited relationships" as they are called in Power BI. That means, referential integrity is not checked. That means practically, that blank rows for referential integrity violations do not show in filters and visuals. E.g. if there was a date in your direct query table that is not in your Dates table, Power BI would not show a "(blank)" row for these missing dates. That means, a category showing the values for the missing dates is not shown, but the values are still included in the grand total. See this example:
The RELATED function is not supported across limited relationships, thus the error. If you import your Excel table to your SQL Server first and then load both tables in Direct Query mode or Dual mode, then you have a normal, strong relationship and RELATED function works.
Read more about limited relationships here: https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-relationships-understand#limited-relationships
BR
MartinHello Martin_D ,
Thank you so much for looking into this. your answer was helpful.
but my only concern is, I can't load the data into Database, it must be an excel sheet coming from sharepoint site. and then I need to create column with related function in my DQ table. If you can suggest any alternative approach.
Thank you.
Hello Martin_D
Also can you brief why it is throwing issue while using import and direct query mode both together, cz when I've tried both table in import mode, it is working fine with relationship as well as related function.Relationship between import mode tables from any source = strong relationship = RELATED() is supported
Relationship between Direct Query tables from same SQL database = strong relationship = RELATED() is supported
Relationship between any import mode table and any Direct Query table = limited relationship = RELATED() is not sopported
Relationship between two Direct Query tables from different sources/databases = limited relationship= RELATED() is not supported
Your situation that is throwing the issue is the 3rd case.
Your situation that works is the first case.
Reason: Functionality as provided by Microsoft.
