Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
hvan-user2
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
Community Champion
Community Champion

@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 Super User!




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

@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 Super User!




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

@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 Super User!




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

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.

 

@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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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