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, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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