cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hvan-user2 Frequent Visitor
Frequent Visitor

Problem with Create Relationship feature

Hi all,

 

I use Direct Query to get data from SQL Server. I was trying to create a relationship between two tables on a key column, which is a primary key in one table and foreign key in the other. It's a straight forward one-one relationship between them.

 

But, when I tried to create a relationship in Power BI desktop, it gave me a warning.

I tried changing the Cardinality, filter direction to different combinations, but it doesn't work. 

Capture.JPG

 

Does anybody have any idea about this kind of warning?

 

Thanks.

 

8 REPLIES 8
kcantor Super Contributor
Super Contributor

Re: Problem with Create Relationship feature

@hvan-user2

If it is a one to one relationship, change it to one to one. Also, remember that filters flow downhill. are both of these fact tables or is one a look up table? How do you plan to use them? You may need to create a lookup table and link the two between them.

If you provide more information on your data we can better help you.

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




hvan-user2 Frequent Visitor
Frequent Visitor

Re: Problem with Create Relationship feature

@kcantor

  • Changing it to one to one didn't work.
  • Table A with primary key is a look up table with 4 million records.
  • Table B with foreign key is also a look up table but is like a extension to table A. Table B has 25 thousand records.
  • I have a fact table which is connected to Table A and for some records, would like to get some details from Table B.
  • I know I can create two relationships from fact table to A and fact table to B, but I dont want to do that unless there is no other alternative.
kcantor Super Contributor
Super Contributor

Re: Problem with Create Relationship feature

@hvan-user2 Have you considered merging the tables into one look up table?



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




hvan-user2 Frequent Visitor
Frequent Visitor

Re: Problem with Create Relationship feature

@kcantor

 

Merging them into a single table will impact the performance. Every time I need something from those 25000 records, my queries will scan 4 million records.

 

Merging them can be a solution but not a perfect one.

 

Thanks for your help.

kcantor Super Contributor
Super Contributor

Re: Problem with Create Relationship feature

@hvan-user2Do you need all of the 4 million rows everytime? We have some robust data models here as well but I hardly ever cross over the million row mark in actual usage.  I generally keep my data models smaller and more agile. I cannot imagine having a look up table that large. Can you share a snippet of data? If you can, the solution could be more specific. In fact, you might be better served with a snowflake data model.



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




hvan-user2 Frequent Visitor
Frequent Visitor

Re: Problem with Create Relationship feature

@kcantor

 

The business team will need them for various vetting purposes. And I'm sorry I can't share any data.

 

Thanks for your suggestion about snowflake data model.

 

All this discussion is good, but I would still like to know what caused the warning when I tried to create the relationship.

v-micsh-msft New Contributor
New Contributor

Re: Problem with Create Relationship feature

Hi hvan-user2,

Have you enabled the cross filtering in both directions for DirectQuery Preview option?

To enable cross-filtering for DirectQuery in Power BI Desktop, select File > Options and settings > Options, then check the box next to Enable cross filtering in both directions for DirectQuery, as shown in the following image.

For more information, check:

Bidirectional cross-filtering using DirectQuery in Power BI Desktop (Preview)

Enable this option and then check to see if the relationship could be created.

 

Highlighted
hvan-user2 Frequent Visitor
Frequent Visitor

Re: Problem with Create Relationship feature

@v-micsh-msft

Hi,

 

Enable Cross Filter in both directions for DirectQuery was already enabled when I encountered that issue.

Not much help from enabling and disabling that feature. It still didn't let me create that relationship.

 

Thanks for your suggestion though.

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 54 members 1,036 guests
Please welcome our newest community members: