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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors