Power BI not bringing correct relationships from SQL Server?

[DMatus]

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.

[v-micsh-msft]

Hi DMatus,

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

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

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]

@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 🙂

[v-micsh-msft]

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]

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.