topic Re: Automatically import One-to-one relationship with Direct Query in Developer

Automatically import One-to-one relationship with Direct Query

Anonymous — Wed, 23 Sep 2020 16:01:40 GMT

I'm trying to make it easier for our team to import tables to reports with the relationships already setup. I was able to create relationships by using foreign keys in the underlying datasources sql schema. However, ideally there are some relationships that should import as one-to-one (currently they are only showing up as one to many).

I tried creating a primary key on one table as the foreign key of another table with a unique contraint. This still imports as one to many. Is there any way to get DirectQuery tables to have a one-to-one relationship automatically recognized?

*Edit* further clarification, it's truely a 1 to zero or 1 as it's possible for there to be no record for some primary key values in the table w/ the foreign key

Thanks!

-Kevin

Re: Automatically import One-to-one relationship with Direct Query

lbendlin — Wed, 23 Sep 2020 21:41:05 GMT

there are several warning flags in what you describe.

Usually 1:1 relationships mean that you should have combined the tables at the source. Having blank values on either end of the relationship is highly un-recommended.

Re: Automatically import One-to-one relationship with Direct Query

Anonymous — Thu, 24 Sep 2020 12:16:51 GMT

Hi, so there is a simple reason why my schema has this. Basically I have dozens of tables that relate to a user table by a userid. Those tables referencing users will either have no record for a user, or a single record for a user.

But lets focus on the actual question - Is it possible in power bi to have relationships automatically import as one-to-one using direct query?