cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DMatus Frequent Visitor
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
Habib Established Member
Established Member

Re: Power BI not bringing correct relationships from SQL Server?

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.

 

v-micsh-msft New Contributor
New Contributor

Re: Power BI not bringing correct relationships from SQL Server?

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

DMatus Frequent Visitor
Frequent Visitor

Re: Power BI not bringing correct relationships from SQL Server?

@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 Smiley Tongue

 

@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 Smiley Happy

v-micsh-msft New Contributor
New Contributor

Re: Power BI not bringing correct relationships from SQL Server?

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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 212 members 2,171 guests
Please welcome our newest community members: