Error message: Excel allows only one filtering path between tables in a data model

[ssolovei]

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:

[Anonymous]

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?

[ssolovei · Accepted solution]

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:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/a08d5599-822b-4354-b730-9fb1d870a7d3/error-...

[Anonymous]

Thank you guys. It helped me. 

[BhaveshPatel]

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.

[ssolovei]

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

[v-shex-msft]

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

[ssolovei]

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.

[RepublicOfSteve]

I'm having the same problem.. let me know if you find an answer