One issue you may have faced during modeling in Power BI and SSAS tabular is getting error on creating relationship between 2 tables and when this new relationship makes a circular path in the model. In this case Vertipaq engine realises an ambiguity in reaching to the third table because there are 2 paths, one directly and another via an intermediate table:
For better understanding I defined a scenario with these 3 tables:
- SalesTerritory
- Customer
- Sales
In our example there is one-many relation between SalesTerritory and Customer to show the customers of each territory, one-many relation between SalesTerritory and Sales and also one-many relation between Customer and Sales.
Suppose we need a report with two slicers, one for SalesTerritotyCountry and one for CustomerName and the list of customers in the second slicer must be filter out by the selected country in the first slicer(cascading). Then by selecting one or more customer the report should show Sales Amount value for the selected customers:
Let’s design the model and create one relationship between Customer and Sales and another between Sales Territory and Sales:
But after linking Customer to Sales Territory you will realise that the relationship is inactive:
And you will get an error when trying to change the relationship to active:
Now we can discuss about how this issue can be fixed. One solution is removing the relationship between Sales Territory and Sales (changing from Star to Snowflake):
There are 2 issues with this method, First it gives lower performance specially when Customer table has huge number of records as filter flows form Sales Territory to Customer and then to Sales . The second issue comes in the case that some of the Sales records have valid value for SalesTerritory and Null value for CustomerKey and you want to filter the measures by Sales Terrirory attributes, in this case the outcome would be incorrect.
Another solution is creating a new table as a reference of Sales Territory in Power Query and link it to Customer:
Now we need to get back to the report and figure out how can build cascade filtering between Sales Territory and Customer using this model. This can be done by creating 2 slicers for Country, one for sales Territory to filter Sales data and another for Customer Territory for filtering values in Customer slicer:
Then we can sync the 2 slicers, so changing one will set the same value to the other one. For this purpose we just need to give them a same group name in Sync Slicer panel:
For the final step we can hide Customer Territory table and the second slicer from the end users :
Summary
Power BI and SSAS models cannot handle a circular relationship between 3 tables and make one the relationship to inactive for avoiding ambiguity in querying the data. The issue can be sorted out by creating a reference table and sync the slicers in Power BI report.
