Forum Discussion
Error message: Excel allows only one filtering path between tables in a data model
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 ssolovei,
>>However, this model works in PowerBI desktop without any problems.
Yes, power bi desktop support the cross filter, you can manually modify the cross filter option.
>>Unforunantly, I was not bale to recreate the model that I attached to my firs post in the SQL Analysis Server 2016.
You can refer to following article to know more about how to use the bi-directional filter.
Bi-directional cross filters for tabular models in SQL Server 2016 Analysis Services
Perhaps you can take a look at below link about use cross filter feature in excel:
Many-to-many relationships in Power BI and Excel 2016
Regards,
Xiaoxin Sheng
Hi Xiaoxin thanks for your quick reply!
I'm using Analysis Services Tabular 2016 with 1200 compatibility mode. 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 I presented above.
I am able to create bi-directional relationship between FactDealToFunBridge and DimDeal only if I remove a relationship between DimFund and FactCashflows.The error message says that "that Excel allows only one filtering path between tables in Data Model". I suspect that if we apply a filter to DimFund it will affect FactCashflows table, but it will also affect DimDeal table and DimDeal table will affect FactCashflows table also. So we will have 2 filtering paths to FactCashflows.
If I remove remove a relationship between DimFund and FactCashflows then I will have only one filtering path to FactCashflows and I am I am able to create bi-directional relationship between FactDealToFunBridge and DimDeal.
I don't see this restriction on the number of filtering paths in PowerBI, but it exists for some reason in Analysis Services Tabular 2016.
