Forum Discussion
Error message: Excel allows only one filtering path between tables in a data model
Hi,
Could you please help me with the following error message. I built a simple PowerBI model consisting of 4 tables. This model works fine in the PowerBI desktop, no error messages. Below you can see a screenshot of this PowerBI model.
Later on, I decided to create a model with the same structure in the Analysis Server Tabular 2016, however, I could not create a bi-directional relationship between FactDealToFunBridge and DimDeal. When I try to create this relationship I receive an error message that you can see below.
Does any one know how can this error be fixed in the tabular model? Why is it possible to create a bi-directional relationship between FactDealToFunBridge and DimDeal in the PowerBI desktop but it is not possible to do the same in the tabular model?
Eror message:
I posted the same issue on other social.msdn.microsoft.com forum and one member suggested a solution to this problem. This solution has worked for me and for him. It looks like Visual Studio has a major bug related to the Bi-directional relationships.
Below is the solution:
8 Replies
I posted the same issue on other social.msdn.microsoft.com forum and one member suggested a solution to this problem. This solution has worked for me and for him. It looks like Visual Studio has a major bug related to the Bi-directional relationships.
Below is the solution:
Bidirectional relationships in Tabular model is only supported in SQL Server 2016. To create a bidirectional relationship like scenario, you have to create two helper tables with unique values to create a one to many relationship which will act as many to many.
For more inforamtion,please visit this BLOG.
Hi Bhavesh,
Thanks for your input!
I was trying to create bi-directional relaionship in the SQL Analysis Server 2016. Unforunantly, I was not bale to recreate the model that I attached to my firs post in the SQL Analysis Server 2016. However, this model works in PowerBI desktop without any problems.
Thaks again
I'm having the same problem.. let me know if you find an answer
Hi,
It worked for 2 Dimensions only. Let me explain my Model. I have 2 Fact tables One Budget and other Actual. I have 9 Dimension Tables. Out 9 Dimensions, one dimension only connected to Actual and Rest all 8 dimensions connected to both the fact tables.
Now When I try to change Filter Direction:=To Both Tables between Fact Actual and Dimension Date, I am keep getting this message: :The relationship you are creating lets you filter Budget by dates, but excel allows only one filtering path between tables in data model. Deactivate existing relationship between the tables or change their direction. This relationship can be added as inactive.
Gurus, pls can you explain?
