Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
DMatus
Frequent Visitor

Power BI not bringing correct relationships from SQL Server?

I have an empty Fact Table which has many-to-one relationships with many Dimension tables in my SQL Server, however, when bringing these tables to Power BI, all relationships show up as 1-1, so when the Fact table eventually gets some data, refreshing the model returns an error because the relationships are incorrect. 

 

This used to work fine in previous versions of Power BI, relationships where stablished correctly between tables even if they were empty, is there any way to fix this? I tried disabling automatic relationship detection but to no avail.

4 REPLIES 4
v-micsh-msft
Microsoft Employee
Microsoft Employee

Hi DMatus,

 

Which auto-detect option has you disabled, is the following one?

File -> Options & Settings -> Options ->Current File-> Data load

13.PNG

I tried to disable it and it worked with no additional relationships created when loading new data. Here I only kept the first option selected under Relationships, please also take a try to select the Update relationships when refreshing Queries and see if it would make any difference.

Also I created with two new table with no values (configured the relationship (* to 1) in SQL SMS), this time Power BI shows the relationship as 1 to 1 with Auto-detect selected, I will make some further test and discuss this with others, will update here if there are any new findings.

Regards

@Habib Yes, changing the relationships to 1-* indeed fixes the refresh issue, it's just kinda cumbersome when your fact tables have too many dimensions 😛

 

@v-micsh-msft Yes I've tried enabling and disabling that button, it had no effect either way, sadly. It did not change the relationships before refreshing either, so an error is produced.

 

I sent a frown and was told they would try to fix it, in the meantime I just went and changed every 1-1 relationship to 1-* manually (which took forever!), thank you both for your time 🙂

Hi DMatus,

Apologize for the late response.

I think the issue here should be related with no Data reference, so Power BI just take the 1:1 relationship instead.

I will try to report this issue from my side.

Regards

Habib
Responsive Resident
Responsive Resident

In case of Fact table as empty you can still change the relationship type from 1-1 to 1-*. In this case when data will start loading, you will not receive any issue.

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.