Forum Discussion

Krupa_Parmar's avatar
Krupa_Parmar
Regular Visitor
3 years ago
Solved

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 m...
Data Protection
  • Martin_D's avatar
    Martin_D
    3 years ago

    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.

Martin_D's avatar
Martin_D
Solution Sage
3 years ago

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
Martin

Krupa_Parmar's avatar
Krupa_Parmar
Regular Visitor
3 years ago

Hello 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.