Reply
Frequent Visitor
Posts: 11
Registered: ‎09-21-2016

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?

Super Contributor
Posts: 1,661
Registered: ‎12-15-2015

Re: Filter direction isn't valid for this relationship

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

Frequent Visitor
Posts: 11
Registered: ‎09-21-2016

Re: Filter direction isn't valid for this 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.

Super Contributor
Posts: 1,661
Registered: ‎12-15-2015

Re: Filter direction isn't valid for this relationship

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

Super Contributor
Posts: 2,492
Registered: ‎07-17-2016

Re: Filter direction isn't valid for this relationship

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

Frequent Visitor
Posts: 16
Registered: ‎11-18-2016

Re: Filter direction isn't valid for this relationship

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. 

Frequent Visitor
Posts: 16
Registered: ‎11-18-2016

Re: Filter direction isn't valid for this relationship

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. 

Highlighted
Super Contributor
Posts: 2,806
Registered: ‎06-25-2015

Re: Filter direction isn't valid for this relationship

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

Regular Visitor
Posts: 20
Registered: ‎05-11-2016

Re: Filter direction isn't valid for this relationship

Regular Visitor
Posts: 37
Registered: ‎06-01-2016

Re: Filter direction isn't valid for this relationship

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.