why a single-only existing relation between fact-table & dim-table is inactive --AdventureWorksDW

[arunaramana]

Hello There,

I have a situation -- I noticed that the only exisiting-single relation between the (Fact-table) FactResellerSales and DimReseller (dimension table) is inactive. I happen to notice it as I am unable to filter FactResellerSales -- total-sales by a Reseller-column in DimResller-table -- as I get the same total across all the Resellers in the row.

My concern is, what could the scenarios a relation could be inactive other than when there are multiple-relations between a given fact-table and dimension-table? 

Would anyone please help me understand the scenario? Please find the screenshot of the model. Thank you for giving your valuable time.

[AlexisOlson · Accepted solution]

You can't have multiple filter paths from one table to another without creating ambiguity.

Since you have a path DimSalesTerritory --> DimEmployee --> FactResellerSales, you can't also have an active relationship path DimSalesTerritory --> DimGeography --> DimReseller -- >FactResellersSales.

This article may help:
https://www.sqlbi.com/articles/bidirectional-relationships-and-ambiguity-in-dax/

[Burningsuit · Accepted solution]

Hi @arunaramana 

This relationship is inactive because you already have a relationship to FactResellerSales, through DimGeography to DimSalesTerrritory to DimEmployee. You can't have two active relationships to the same table, no matter how many intermediate tables it goes through.

You'll see you also have an inactive relationship from DimSalesTerritory to FactResellerSales because it also goes through DimEmployee.  

You can elect to make the most frequent relationship active, then use the DAX USERELATIONSHIP function to make the other relationship active for measures that need it.

Hope this helps

Stuart  

[arunaramana]

Thanks for your support, Staurt!

[AlexisOlson · Accepted solution]

You can't have multiple filter paths from one table to another without creating ambiguity.

Since you have a path DimSalesTerritory --> DimEmployee --> FactResellerSales, you can't also have an active relationship path DimSalesTerritory --> DimGeography --> DimReseller -- >FactResellersSales.

This article may help:
https://www.sqlbi.com/articles/bidirectional-relationships-and-ambiguity-in-dax/

[arunaramana]

Thanks, Alexis... 

I remember the point you explained in the response; I think I overlooked the relationship path. Thank you agian!