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