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
mjohannesson
Advocate I
Advocate I

Filter direction isn't valid for this relationship

I try to create a relationship two views, but Power BI Desktop complains that "The filter direction you selected isn't valid for this relationship". The "many" view has a foreign key int column which references the "one" view's primary key int column. Currently, the "one" view contains three rows (PK 1, 2, 3) and the "many" view contains two rows (FK 2, 3). Any idea why I'm not allowed to create this relationship?

13 REPLIES 13
TMock
Frequent Visitor

Has the there been a resolution to this issue?

It's so silly!!!  I was going crazy for nothing!

You have to make relation from the parent table to the main!!!! We all made from the main to the slave!

GRRRR!!!! 

Anonymous
Not applicable

I also run into the exact same issue when I try to create a relationship between a calculated column or a with a field in a query. Appreciated if someone can help overcome this issue.

 

Cheers. 

Anonymous
Not applicable

Hi,

 

I just faced the same issue in my report using direct query on PK FK with the same datatypes. I fixed it by hitting enter, and the relationship worked again. I can try to reproduce it if it's still necessary? 

 

 

 

v-ljerr-msft
Employee
Employee

Hi @mjohannesson,

 

Are you trying to set the relationship's cross filter direction to ‘Both’ when you create the relationship between the two views?

d.PNG

There are some situations where Power BI Desktop cannot set a relationship's cross filter direction to ‘Both’. Cross filtering both directions works well for a pattern of star schema like below.

candmrel_crossfilterstarschema.png

However, cross filtering direction does not work well with a more general pattern often found in databases, like in the following diagram.

candmrel_crossfilterwithloops.png

So please go and check the pattern of your table relationships and follow the article below to create and manage relationships in Power BI Desktop correctly.Smiley Happy

https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-create-and-manage-relationships/

 

Regards

I´m having the same problem and it doesn´t work. The columns types are exactly the same, but it is imposible to create the relation. Moreover, it let me create the relation with another columns with different types (numbers, string, dates...). 

 

I think this is an error. 

 

It would be find that Power BI shows more detail in that type of errors, because many times it´s almost immposible work with this tool. 

@JorgeDiego@mjohannesson@v-ljerr-msft@ankitpatira

I just ran into this bizzare behavior yesterday/today. Using Direct Query against a SQL database. There were 3 records in one table that matched 3 records in another with additional metadata. For the life of me I couldn't figure out why this was error happening. I ended up re-creating the tables as just heaps and STILL saw the same behaviour... The values being compared didn't matter, I tried to pull in different columns, remove all values except the 2 relationship columns, and many other database side things and still, nothing worked. 

Changing any of the fields (Cardinality, filter direction, etc, etc in the "Edit Relationship" pop up has no effect and you can't select "OK".

So I was unable to resolve or re-produce the issue, but I did stumble upon a solution that worked - although I have no idea why.

 

When the Edit Relationship pop up appears with the relationship error and the "OK" button is still grayed out... Just hit "Enter", and the relationship is created.

You then have the same ability to edit it, change the direction, etc, etc. and the relationship works as expected.

 

What if you delete the relationship, and try to re-create it? - You get the same warning message and the inability to create it. Hit "Enter" on the edit relationship pop up, and it gets created.

 

Summary:
I have no idea why this is happening, and I can't recreate a scenerio where I can consistently get the pop up. My only guess is that it is something in the data based on all the checks I've done and testing around different objects and data access methods. Until I can repeat it, I won't file it as an issue, but hopefully if someone encounters this again hitting "Enter" on the Edit Relationships screen with the error will create the relationship as it did for me. At least that part was repeateable!

 

Ironically, if I import this same data set instead of direct query it - Power BI creates the exact same relationship automatically for me.

Hope this works/helps someone else.


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

Had this precise issue.  What I have is a 1:1 relationship.  There is no mismatch.  There is exactly 1 row in the "parent" for each row in the child.  No matter what I do, I get this error.  If I just slam the Enter key, it creates the relationship, even though the OK button is greyed out.  I can then open the relationship back up and not only is it created, it has zero problem recognizing it, produces no errors, and the OK button is enabled.

Anonymous
Not applicable

I´m having the same problem and it doesn´t work. The columns types are exactly the same, but it is imposible to create the relation. Moreover, it let me create the relation with another columns with different types (numbers, string, dates...). 

 

I think this is an error. 

 

It would be find that Power BI shows more detail in that type of errors, because many times it´s almost immposible work with this tool. 

ankitpatira
Community Champion
Community Champion

@mjohannesson check that data type of both the columns is of same type. If it is then it shoud let you create that relationship.

Thanks @ankitpatira, but they are the same type. Both are int, not null in the database and whole numbers in Power BI Desktop's query editor. Must be something else I'm missing.

@mjohannesson I can't really think of any other reason you won't be able to create that relationship. Have you checked that yu've selected correct (one to many) under cardinality and tables.

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.